Calculating Running Totals in Power BI Using DAX

Running totals are a common analytical insight required within organisations. Here I show you the simple logic required in DAX to get visuals of this type of analysis. Good luck with it!

***** Video Details *****
1:42 calculating running total in 30 days
3:58 calculating running total in 90 days
4:30 calculating DATESINPERIOD

***** Learning Power BI? *****

***** Related Links *****

***** Related Course Modules *****

***** Related Support Forum Posts *****

[EnterpriseDNA]

Learning Power BI?

Related Links

Related Course Modules

Related Support Forum Posts
Cumulative Sum Of A Measure That Creates Another Cumulative Sum
For more running total support queries to review see here….

[rapatel]

Awesome video. I have a rolling rate to calculate, which is the division of one rolling sum measure over another rolling sum measure. Though the individual rolling sum measures are correct, the rolling rate from dividing measure 1 over measure 2 is incorrect. Why is that?

[quynhhoa]

hello, how can you calculate running total when there's no transactions in the sales table in the specific period. EX. i need to see running total by each week. when there's no transaction in a specific week, my line chart breaks and return blank. I'd like a continuous chart to show if there's no transaction in that week, the value would return the value from previous week instead.

[MrJuvette]

Where can we download the data to start with in order to follow along??? it is a minus from me.

[roseslillies]

What if i need the cumulative sum till the date filter on my report?

[shailz]

Hey! I am confused in max function in datesinperiod Dax.Please anyone help me out.

[guillaumeledique]

Hi Sam, After spending roughly a full day trying to figure out how to fix historic values within a dynamic report--people wanted to see current month values regarding invoicing and targets, as well as order values through the end of the selected period, I was ready to throw the towel into the ring. My main issue had been the slicer selection which was causing issue (month selection on invoicing is easy if you are only looking at fiscal year invoicing, however, things get a little more complicated when orders being monitored are older than current fiscal year).I had been hoping on fixing the issue using CUBE formulas as the report is not using PowerBI, meaning that I've converted a pivot table to Cubevalues... however, getting the dynamic date selection working was proving too difficult.Thankfully I punched in the correct search criteria and got to your video which is a life saver!!!Thanks,
Guillaume

[diphetu]

I want to calulate similar Cumulative Total Internet Sales for a given fiscal year, previous-year cumulative total for Internet Sales for a given fiscal year, AND calculate the relative cumulative YOY variance.I have used this Dax Formulas can you please help me isthis right or Total Internet Sales for a given fiscal year **Internet Sales YTD =
IF (
    COUNTROWS ('Internet Sales' ) > 0,
    CALCULATE (
        SUM('Internet Sales'[Sales Amount]),
        FILTER (
            ALLSELECTED ( 'Date'),
            'Date'[DateKey]      <= MAX  ( 'Date'[DateKey] )
          
        )
    ),
    BLANK ()
previous-year cumulative total**
Internet Sales PYTD =
IF (
    COUNTROWS ('Internet Sales' ) > 0,
        CALCULATE (
                     SUM('Internet Sales'[Sales Amount]),
                            DATESBETWEEN (
                              'Date'[Date],
                             DATE ( YEAR ( MAX (  'Date'[Date] ) ) - 1, 4, 6 ),
                             MAX (  'Date'[Date] )
                             )
                          ),
    BLANK ()

**The relative cumulative YOY variance**
Internet Sales YoY =
DIVIDE( ([Internet Sales YTD] - [Internet Sales PYTD] ), [Internet Sales PYTD], 1)

[jayxpea]

instead of using MAX DATE, if today() is used, I assume it gives the same result?

[shoaibrehman]

Sir I am watching fantastic full of information packages.
But i stuck in one senerio can't find help.
Would you please assist me.
Suppose we have products A, B and C which are ordering in a month different days. I want running total of all product each month. Next month running start again means running total for each month not all 3 months.
I want solution in DAX.
I can calculate running total of 3 months but can't each month.
Thanks in advance

[ajaaskelainen]

The "DatesInPeriod"-formula was an eye-opener for me. Thank you!

[mariners]

Great video. This is such powerful DAX. Thanks for posting!!!

[BitTeluguGaming]

NEED SOLUTION :
Table 1 Continents
Table 2 Covid Data (join by continent id)
I have a slicer from table 1 and im displaying table 2 values in a table visual
I have a dax function like
na value = var a = [sumcases] return if(isblank(a), "n/a", a)
when i use the above measure and when a select a continent it is displaying country and totalcase (with n/a if values are blank) AND ALSO IT IS DISPLAYING ALL OTHER COUNTRIES BELONGING TO OTHER CONTINENT TOO, DISPLAYING TOTALCASES AS N/A
HOW CAN I SOLVE THIS. PLEASE HELP 🙏 . THANKS IN ADVANCE

[benjaminanic]

Hi Sam - thank you for the content. I have a unique cumulative total situation I’m hoping you can help with. I have an initial contract budget value column (one record) in one table and a column with many contract change orders in another table. How can I create a running total that starts with that initial contract budget value column and the running total is based on contract change orders. Let me know if you can help! Thanks in advance!

[appolineodhiambo]

Hi all reading this comment, assist. i am doing some reports on sales. i have opening inventory, sales and shipments. i want to loop this data such that my closing invetory for january becomes my opening inventory for February and at the same time not affect opening inventory of January

[liranzaidman]

I don't have enough words to thank you

[sanjayshukla]

Hi Sam - Big fan of your videos. Wanted to check if we can do opening balance and closing balance calculation for dataset having missing periods (months) which is very common when there are no stock movements? I tried using following calculations but these doesn't work when there are no stock movement for any period.

Opening Balance =, Inventory[PERIOD])

--Closing Balance Formula as RunningTotal
RunningTotal_CBQ = CALCULATE(SUMX(Inventory, [Total_Qty]), FILTER(ALL(Inventory[PERIOD]), Inventory[PERIOD] <= MAX(Inventory[PERIOD])))

Total_Qty = SUMX(Inventory, Inventory[QTY])

Please let me know how should I proceed ?

Thanks in advance.

Sanjay.

[DanielWeikert]

Great work! Is there a way to create the running total exactly by month? (instead of 30 days, either 31, 30 or 27/28 for February)? Thanks

[rrrprogram]

Greatt video again... thanks ...

would love to see some thing more complex dax query or formula

[muhammadovaisjahanzaib]

How To Sum On Current Month Total Amount By Date Filter