Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How to calculate the cumulative totals of measure in following example:
The model contains a date table and fact table called Internal.The measure EOMClosed is obtained by using
EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]))
The measure when sliced by Date dim gives me number of tickets that got closed each month.I want to make it like cumulative/running.
EOMOpen is number of open tickets each month sliced by date like :
EOMOpen = CALCULATE(DISTINCTCOUNT([ID]),FILTER(ALL(Internal),(Internal[CloseDate]=blank()||Internal[CloseDate]>EOMONTH([OpenDate],0))&&Internal[FirstDate]<=max('Date'[Date])))
I tried to do the same with EOMClosed but turns out the userelationship is not propogating the context to filter function :
EOMClosed = CALCULATE(distinctcount(Internal[ID]),userelationship(Internal[CloseDate],'Date'[Date]),filter(Internal,[CloseDate]<=max(Date[Date]))
Pls guide me to fix the EOMClosed to get cumulative values.
Here is a sample file:
Solved! Go to Solution.
Hi @Anonymous,
Try this formula, please.
EOMClosed = CALCULATE ( DISTINCTCOUNT ( Internal[ID] ), USERELATIONSHIP ( Internal[CloseDate], 'Date'[Date] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= EOMONTH ( MAX ( 'Internal'[OpenDate] ), 0 ) ), ISBLANK ( Internal[CloseDate] ) = FALSE () )
Best Regards,
Dale
Hi @Anonymous,
Try this formula, please.
EOMClosed = CALCULATE ( DISTINCTCOUNT ( Internal[ID] ), USERELATIONSHIP ( Internal[CloseDate], 'Date'[Date] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= EOMONTH ( MAX ( 'Internal'[OpenDate] ), 0 ) ), ISBLANK ( Internal[CloseDate] ) = FALSE () )
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |