Forum Discussion

JulietteGSA's avatar
JulietteGSA
Frequent Visitor
8 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    8 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

  • Anonymous's avatar
    Anonymous
    Not 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-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi JulietteGSA,

     

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

     

     

    Best Regards,
    Dale

  • v_ichharam's avatar
    v_ichharam
    Frequent Visitor

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