Cumulative Total - Changing start date

[JulietteGSA]

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!

[v_ichharam]

Using "all" allows me to keep history in the calculation even when filtering out periods

[v-jiascu-msft]

Hi @JulietteGSA,

Did you solve your issue? Could you please mark the proper answer as solution?

Best Regards,
Dale

[Anonymous · Accepted 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] ) )
    )
)