Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GillyGils
Frequent Visitor

Optimize Running Total

Hi All,

I'm currently running the following DAX code for a Running Total based on a calculated measure. I'm looking to optimise the below DAX code?

 

VAR _Date = MAX ('Fact_Table'[Date])
VAR _Date11 = EOMONTH (_Date, -11)
VAR _Date10 = EOMONTH (_Date, -10)
VAR _Date9 = EOMONTH (_Date, -9)

 

VAR _Value11 = CALCULATE([Head Count],
FILTER(
ALL('Fact_Table'), 'Fact_Table'[Date] = _Date11)
)

VAR _Value10 = CALCULATE([Head Count],
FILTER(
ALL('Fact_Table'), 'Fact_Table'[Date] = _Date10)
)


VAR _Value9 = CALCULATE([Head Count],
FILTER(
ALL('Fact_Table'), 'Fact_Table'[Date] = _Date9)
)

 

RETURN _Value11 + _Value10 + _Value9 + [Head Count]

 

Example of results below:

GillyGils_0-1683891459508.png

 

 

Thanks.

1 ACCEPTED SOLUTION

Hi, @GillyGils 

 

You can try the following methods

Measure:

Running Head Count = SUMX(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Head Count])

vzhangti_0-1684823300203.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@GillyGils  Use a date table and try like. Date table's date should be joined with date of your table. Use date/month from date table in visual, slicer and measures

 

CALCULATE([Head Count],filter(allselected(date),date[date] <=max(date[Date])))

or

 

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

 

or

 

CALCULATE([Head Count],filter(all(date),date[date] <=max(date[Date])))

 

or

 

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

 

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Thanks for the response.

In my fact table, its only monthly data. So I only have month end dates so which are a whole number in which ive converted to 'Date'. Is there a solution to not have a date table included?

Hi, @GillyGils 

 

You can try the following methods

Measure:

Running Head Count = SUMX(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Head Count])

vzhangti_0-1684823300203.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.