cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
PBrainNWH
Helper I
Helper I

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?). Here are the two measures for those columns:

TotalActual = SUM('Combined VE PL Pared (2)'[Actual Amount])
RT Actuals YTD = IF(ISBLANK([TotalActual]),BLANK(),TOTALYTD([TotalActual],'Calendar'[Date]))


PBrainNWH_0-1695256693798.png

The real issue I'm having is the RT Actuals FAN/FCM/SCM columns. I'm trying to apply a filter on each but it looks like it's just bringing in the totals and not running, here is the syntax for those measures (example is for the 30-FAN column):

RT Actuals FAN = IF(ISBLANK([TotalActual]),BLANK(),TOTALYTD([TotalActual],'Calendar'[Date],FILTER('Combined VE PL Pared (2)','Combined VE PL Pared (2)'[Department Code]="30-FAN")))
 
Am I applying the filter wrong? Help! And if there's a way to get the totals to show on the total row, that'd be a bonus.

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

 

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")

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])))
 
PBrainNWH_0-1695267475518.png
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!!!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors