Power BI DAX: Running Totals Using Variables, CALCULATE, and FILTER

In this video, I show how you can make a calculated column that will return a running total of values. By using variables and the function CALCULATE with the DAX language you can filter your calculate to run on a specific amount of dates as well as a filtered value from another column.
If you enjoy this video or any of my other videos and are interested in formal training on Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Matt20" to get an extra of 20% off at check out when purchasing any private training or On-Demand Learning classes from pragmaticworks.com/pricing/ #MatthewPeterson
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning Courses FREE Trial: www.pragmaticworkstraining.co...
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks.com/pricing/
🔗Pragmatic Works Boot Camps: pragmaticworks.com/boot-camps/
🔗Pragmatic Works Hackathons: pragmaticworks.com/private-tr...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks.com/virtual-me...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks.com/private-tr...
🔗Pragmatic Works Blog: blog.pragmaticworks.com/
Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️KZbin: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
Email: [e-mail]

[@28goldenboy]

This was by far the best, easiest, and most concise explanation for running totals on PowerBI! Thanks a lot for putting this together.

[@sabarashid1513]

You are a life saver! I was searching exactly for this - running total for date and category together. Everywhere I searched, it was either only by date or category. Thanks alot!

[@nampai]

It took me an eternity to find this solution online. Thank you so much!

[@PragmaticWorks]

You are welcome! Glad we could help provide a solution for you!

[@Mj27th]

Probably the greatest video I've ever seen explaining a DAX formula for Power BI. You have a new subscriber from me. I will be watching all these videos

[@ginebranordes]

I have been trying to do this for days!!!! Thank you so much! I actually finally understand how filtered tables work. Just amazing!

[@Serraomomma]

Well done, well said, the pace is excellent, the explanations are clear and you are appreciated! Looking forward to the next one!

[@xalladus]

I watched way too many videos on this, and this was the first one that actually made sense to me. Even though it wasn't the exact solution I needed, the way it was explained made it easy to adapt to my project. Thank you so much! I only found out about power BI 3 days ago, I am so behind.

[@PragmaticWorks]

You're welcome! Thank you for your comment (:

[@patricktemple2724]

Great video, spent ages watching other videos and reading through online guides but this one sorted my issue straight away!

[@PragmaticWorks]

Happy to help!!

[@RakeshSaha1705]

Thank you Sir. Along with the way you have shown here, I made a little modification to get a S curve which is restricting a cumulative value upto a certain date. This video is a super help.

[@marxoman]

It took me only 5 hours to find this video, verry helpfull since i need to have multiple running totals for diffrent suppliers.

[@denyskorcheniuk5920]

Sir, you have just saved a couple of days of my life. Thank you for your tutorial.

[@PragmaticWorks]

You are most welcome

[@selcukkartal6724]

I was searching for a proper way of calculate func with dynamic filters. Video helped me a lot. thank you

[@veebee3969]

Thank you. Great video. Clear explanations. I worked out the example with jumbled up Agency rows and ended up with the correct answer.

[@user-oj5fb3qt6k]

By Far The Best Explanation so far

[@sujeshfca5723]

I watched and learned easily from Your Video. Thank you first of all. Yes you are the best teacher and know how to present , Explain and transfer it to the mind of the listener. I really appreciate this.Excellent

[@PragmaticWorks]

Thanks so much for the feedback!

[@meilstone]

Thanks for this great explanation! That filter even works on the grand total below!

[@Agxo1]

Wow this was amazing. I spent days trying to figure this out. Thanks!!

[@PragmaticWorks]

Glad it helped!

[@jhongagaful]

You have saved my life, totally clear. TY

[@BrosseauFarms]

thank you thank you thank you!!!! I've been racking my brain on making a perpetual inventory column and you solved my problem!!!! you now how a loyal new subscriber.

[@PragmaticWorks]

You're very welcome!

[@peterblenard9161]

Thank you so much! This has been puzzling me for days!

[@fromtonghua5018]

Excellent tutorial. Explained step by step.

[@margotkk]

This is a really useful tutorial - thanks for posting it!

[@jamesdeanstephens]

Thank you! I was really trying to find something that wasn't a measure in this specific project that I'm working on.

[@jeromeastier462]

Exactly what I was looking for. Thanx! Plus it’s clear and simply put!

[@PragmaticWorks]

So glad you liked the video Jerome, thank you for learning with us!

[@adammazeli]

thank you so much for the help. blessed you sir

[@ikaridan]

😎Your didatic is very clear, it´s rarely in youtube, thanks for share your knowledge!!!

[@MrThapaliya233]

Very well explained, thank you!

[@PragmaticWorks]

You are welcome!

[@jamilbouchaibi8585]

Exactly what I was looking for. Tx

[@rubenaugustine8666]

Thanks, Matt

[@oscarsantanafernandez5719]

Excellent video. Great explaining and showing of the programming! Just suscribed to the channel!

[@PragmaticWorks]

Welcome aboard! Thank you!

[@imaneess4079]

Thank you very much for the explanation. Very well explaned and very helpful :)

[@joa0liveira92]

Awesome explanation! Easy and direct to the problem.

[@PragmaticWorks]

Great to hear!

[@rewave2297]

It was very helpful, thanks a lot!

[@PragmaticWorks]

Glad you enjoyed it!

[@daniyarberik9492]

🎉 you’re best!!! Thank you so much!

[@chaolimeng8926]

Hi, I found your Channel really helpful, you can explain complicated things quite easy to understand. Do you happen to have a video explaining cardinalities in iterator and relationship? Thank you very much!

[@sepehreftekharian8659]

It was the exact same solution that I was looking for days. Thank you so much. Now the question is how can you show that in clustered and line chart? Thanks

[@dribeiro9564]

Thanks for the video!!!!

[@PragmaticWorks]

Thanks for watching!

[@carolalves7807]

Thank you for the video, it was really helpful! Just another question, is there a way to filter the table, so it just shows the last row for each branch, without it messing out with the calculation?

[@julie_chen]

Thank you.

[@PragmaticWorks]

You're welcome (:

[@kennethstephani692]

Great video!

[@PragmaticWorks]

Glad you enjoyed it!

[@fadwa2413]

Thank you so much,Please Keep Going.

[@PragmaticWorks]

Glad you enjoyed!

[@Tahuti418]

Great thank you!!

[@PragmaticWorks]

Glad it was helpful!

[@Matteinko]

Oddly this is one of those videos online that, if learnt by heart, can land you a GOOD JOB and at this date it has ~650 visualizations 😂😁

[@MrThapaliya233]

Agree!

[@lakeshed]

Totally agree

[@laalaajonsen]

You think this example is that highly sought after? Interesting

[@elipomerantz6980]

Great video

[@PragmaticWorks]

Glad you enjoyed it

[@walidkhlil2872]

thanks

[@PragmaticWorks]

You're welcome!

[@frezerzelalem4017]

This is great solution.Thanks! I tried to recreate as exactly what you did with VARIABLES and it worked. But tried without the variables and it didn't work correctly, instead returned the total sum of all values (72). Not sure why

[@dcw888]

Thanks for your solution and it worked in my problem well. However, when I tried to slice the data using a slicer, the DAX does not take the sliced data into account. The DAX calculation still ignores the slicer. For example, if you create a slicer on Sales[Date] in your example, when you slice it, the DAX calculation will not change in accordance to the slicer.

[@lucianmuntean5534]

What if you have for the same Agency 2 rows with the same date but diffrent sales no. then the summ won't work for the columns with the same date

[@xuyi2893]

Not sure whether you can introduce if we want to create the measure instead of adding the column. I tried to add the columns it works, but it doesn't work if I want to create the measure. Would you help to introduce that? Thanks! But really appreciate that video.

[@jayatushar]

Nice...hlelped me. However a question -- do we have to have the records sorted in ascending order of the date? or does system/Dax take care of this?

[@thithanhhuyenpham9961]

Thank you so much. My case has a problem that the same Agency had more than 1 transaction on the same day. Ex: 1/1/2021 Agency A transacted 2 times. As a result, the running total of 2 transactions is equal. Hope you give me a solution.

[@williamarthur4801]

Really interesting approach, and one to add to my list of 'how to', do you think it has any advantage over just ( and I do mean for a column, never a measure ), using 'EARLIER' and just decaling a VAR for 'agency' or my own favorite, in the case of above; CALCULATE, SUM(Sales[Sales] , FILTER(ALLEXCPT(SALES, SALES[AGENCY] ), SALES [DATE] < = EARLIER ( SALES[DATE]) ) ) Which does away with declaring agency as a variable and using && to join to earlier. I am in power pivot more than bi.

[@jeffreyropp]

Why does the running total get summarized when used in a chart? My similar sample data looks as expected in a table visualization or in the data view, but in a chart context the new column gets summarized without an option to not do so.

[@philipstern2929]

This video has helped me a lot! Could you tell me if there is any solution to make the running totals filter by year in a new column? I want to make my Running Totals start from 0 when a new year starts but I find no way to make that work in a new column. Thanks a lot!

[@TheAlgebraTeacherPOWERStoBI]

Hi Philip. I'm guessing you would want to use the time intelligence DAX function TOTALYTD to accomplish that scenario.

[@shuha24]

Great content as usual. But I think this process is very memory intensive.

[@jrin8927]

That's exactly what I'm looking for! Just one tiny thing... Is there any way to transform this formula to use it in a DirectQuery example? That would be awesome, thank you!

[@510z2]

If you are using Direct Query then you can create the column with SQL code using window functions

[@macshock632]

This is cool! but what if i need the running total value showing up at certain dates (month End). How would I do that?

[@surender665]

Hi please help me, how to calculate running total exclude current month

[@rcarranzamanuel]

What happens if sales jumps from Nov to Jan, no dec information?

[@dataanddailylife]

I want to do running totals by membership and loop for 12 months from the start date buy goods. How do this? Please help me.

[@user-rb3py2dv4o]

When I try to create a new column by following your process, it’s not letting me reference another column while defining the variable. Just shows a list of all existing measure. Please help because creating a custom column is not fun.

[@allendataguy]

Why don't we use the formula "Rolling sales total = calculate(sum(Sales_table[sales]), Date_table[Date_column]

[@atifhussain7935]

How we can separate the agency "A" & Agency "B" in two columns instead of all value is in one running total column ??

[@georgecook5120]

Shouldn't the Rolling Total column sum to 72? Since we are using a rolling total for each agency, it would be 38 + 34 = 72. Isn't this the correct answer?

[@hristinabiserinska4388]

Do you have a next video where you return the Date when reached certain running total amount? Or let me ask it as a more general question - how to calculate a median value of a dataset that is aggregated, meaning has frequencies - 10 times value X, 2 times value Y presented in 2 rows in a table, instead of 12 rows. Therefore, Median formula is not helpful. Thank you!

[@christinelowery2415]

What about running totals for items per dc per Region per week and per snapshot date? Whew that’s a lot to explain

[@christinelowery2415]

Forgot I need 4 weeks forward each time😊

[@dianajimenez9574]

I get an error message that says, "a circular dependency was detected: EqAuditsReq[Column]. This is my code: Column = var CurrentMonth = EqAuditsReqRT[Month Number] var CurrentBranch = EqAuditsReqRT[Branch] var FilteredTable = FILTER(EqAuditsReqRT, EqAuditsReqRT[Month Number] = CurrentMonth && EqAuditsReqRT[Branch] = CurrentBranch) return CALCULATE(sum(EqAuditsReqRT[Equipment Audit Requirement]), FilteredTable) Can anyone help?

[@petecardona8203]

So many formulas just for a simple row sum total? Easier in excel

[@m3abadi404]

This will give wrong results fir repeated dates fir sam agency !!!

[@christinelowery2415]

Wouldn’t you sum the sales by grouping agency and date first?

[@aryansena7290]

Total is not correct