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.
Hi everyone,
I have a problem with my cumulative totals : I have been able to create measures that calculate cumulative totals but it does not adapt to my dashboard filters.
This is what I wrote :
In_Full_Agg = IF(
AND(MIN('Date'[Date])<=CALCULATE(MAX('Cube OTIF'[Date]);ALLSELECTED('Cube OTIF'));MAX('Date'[Date])>=CALCULATE(MIN('Cube OTIF'[Date])));
CALCULATE(SUM('Cube OTIF'[Nb Orders In Full - SI]);
FILTER(ALLSELECTED('Date'[Date]);'Date'[Date]<=MAX('Date'[Date]))))
For instance, I have a "Date" slicer,
and I would like my measures to adapt to it and calculate the cumulative totals ONLY on the period selected, so that if I choose 4th of March - 23rd of April it takes the 4th of march as a start date. Right now when I do that the value displayed for the 4th of march is the cumulative total calculated starting from the 1st of January, not the raw value.
I don't know how to "connect" my dashboard filter to my measure, it seems that the dashboard slicers only filter the display but doesn't have any effect on the measure itself.
I have tried to add another filter on the minimum date in my formula, like that :
In_Full_Agg = IF(
AND(MIN('Date'[Date])<=CALCULATE(MAX('Cube OTIF'[Date]);ALLSELECTED('Cube OTIF'));MAX('Date'[Date])>=CALCULATE(MIN('Cube OTIF'[Date])))
;CALCULATE(SUM('Cube OTIF'[Nb Orders In Full - SI]);
FILTER(ALLSELECTED('Date'[Date]);'Date'[Date]<=MAX('Date'[Date]));
FILTER(ALLSELECTED('Date'[Date]);'Date'[Date]>=MIN('Date'[Date]))))
And well.. I have no idea what is happening but it is NOT what I'm looking for.
It doesn't seem to do any cumulative totals anymore....
I hope someone will be able to help me 🙂
Thank you!
Solved! Go to Solution.
have you tried using just ALL instead of ALLSELECTED in your first measure as that may be causing issues.
In_Full_Agg = IF ( AND ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Cube OTIF'[Date] ); ALLSELECTED ( 'Cube OTIF' ) ); MAX ( 'Date'[Date] ) >= CALCULATE ( MIN ( 'Cube OTIF'[Date] ) ) ); CALCULATE ( SUM ( 'Cube OTIF'[Nb Orders In Full - SI] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
Using "all" allows me to keep history in the calculation even when filtering out periods
Hi @JulietteGSA,
Did you solve your issue? Could you please mark the proper answer as solution?
Best Regards,
Dale
have you tried using just ALL instead of ALLSELECTED in your first measure as that may be causing issues.
In_Full_Agg = IF ( AND ( MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Cube OTIF'[Date] ); ALLSELECTED ( 'Cube OTIF' ) ); MAX ( 'Date'[Date] ) >= CALCULATE ( MIN ( 'Cube OTIF'[Date] ) ) ); CALCULATE ( SUM ( 'Cube OTIF'[Nb Orders In Full - SI] ); FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) ) ) )
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 |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |