Forum Discussion
Cumulative Total - Changing start date
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!
- Anonymous8 years ago
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] ) ) ) )
3 Replies
- AnonymousNot applicable
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] ) ) ) ) - v-jiascu-msftMicrosoft Employee
Hi JulietteGSA,
Did you solve your issue? Could you please mark the proper answer as solution?
Best Regards,
Dale - v_ichharamFrequent Visitor
Using "all" allows me to keep history in the calculation even when filtering out periods