Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have built the following DAX formula that succesfully provides a cumulative total value by category:
"CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),
'Table'[Date]<=MAX('Table'[Date])),
VALUES('Table'[Category])
)"
The issue I have is each category has a different MAX date, therefore in the Area Chart the cumulative total drops if one catgeory has a MAX date higher than another.
Any ideas how to get round this?
Solved! Go to Solution.
hey @SJCee ,
you need first to create the cumulative measure like, (using the sample model I used):
Hope it works for you.
Cheers, LQ
hey @SJCee ,
you need first to create the cumulative measure like, (using the sample model I used):
Hope it works for you.
Cheers, LQ
Hey @SJCee ,
I've a solution to your problem, hope it works for you
the sample table I used to simulate your problem was:
1) Create a Date Table (if you don't have one):
2) create a relationship between the original table and the new date table
Create a new meeasure like:
New Measure =
VAR _Date =
SELECTEDVALUE ( Table1_Dates[Date] )
VAR _Last =
CALCULATE (
LASTNONBLANKVALUE ( Table1_Dates, SUM ( Table_1[Value] ) ),
Table1_Dates[Date] < _Date
)
RETURN
IF ( SUM ( Table_1[Value] ) = BLANK (), _last, SUM ( Table_1[Value] ) )
with this code your visual will looks like
the future dates will be filled in with the last non empty value.
Hope this helps.
Cheers, LQ
Thank you @LQuedas, I think it is almost there, just wondering how I would edit your formula in order to make it cumulative?
Sam
Hi @LQuedas ,
Thank you very much for your response.
This what the chart looks like:
As you can see the chart works perfectly until around 2027. The reason is the MAX date in the three categories differs e.g. dark blue max date is in 2027, light blue in 2028 and orange in 2031. Is there a way I can edit the formula so that three categories are using the same max date?
Thanks,
Sam
Hey @SJCee ,
Can you screenshot the visual showing the issue? I did some tests here and your formula seems work fine... so I need to understand the visual configuration.
Cheers, LQ
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |