Forum Discussion

PBrainNWH's avatar
PBrainNWH
Helper II
2 years ago
Solved

Help with Running Totals

I'm trying to get some running totals, The first column is fine, that's just the total amount each month. The second column seems to be working except it's not producting the total (is that normal?)....
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    PBrainNWH , You are using totalYTD, which for year till date not running total. Also even if removed a month it will still build from the start of year

     

    You can use running total with allselected to honor filters

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

    Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

    Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

     

     

    Windows function is also used YTD, just use partition by year

     

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

     

amitchandak's avatar
amitchandak
Super User
2 years ago

PBrainNWH , You are using totalYTD, which for year till date not running total. Also even if removed a month it will still build from the start of year

 

You can use running total with allselected to honor filters

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

 

 

Windows function is also used YTD, just use partition by year

 

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

 

  • PBrainNWH's avatar
    PBrainNWH
    Helper II
    2 years ago

    I was able to get this to work! Thank you.

    Cumm Actuals = CALCULATE(SUM('Combined VE PL Pared (2)'[Actual Amount]),filter(all('Calendar'[Date]),'Calendar'[Date] <=max('Calendar'[Date])))
     
    Two questions:

    1) How can I add an ISBLANK() function to this so the total isn't repeated for months that don't have anything?
    2) How do I add this filter to the Cumm Actuals measure?

    FILTER('Combined VE PL Pared (2)','Combined VE PL Pared (2)'[Department Code]="30-FAN") 

    I've been trying both, but getting errors with every attempt.

    Thanks!!!
  • PBrainNWH's avatar
    PBrainNWH
    Helper II
    2 years ago

    I got the filter to work with this:

    RTAct FAN = CALCULATE(SUM('Combined VE PL Pared (2)'[Actual Amount]),filter(allselected('Calendar'[Date]),'Calendar'[Date] <=max('Calendar'[Date])),'Combined VE PL Pared (2)'[Department Code]="30-FAN")