cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JulietteGSA Frequent Visitor
Frequent Visitor

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.

 

 

Capture d’écran (10)_LI.jpg

 

Capture d’écran (9)_LI.jpg

 

 

 

 

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. Capture d’écran (12)_LI.jpg

 

 

It doesn't seem to do any cumulative totals anymore....

 

I hope someone will be able to help me 🙂 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
ThomasFoster Established Member
Established Member

Re: Cumulative Total - Changing start date

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] ) )
    )
)

View solution in original post

2 REPLIES 2
ThomasFoster Established Member
Established Member

Re: Cumulative Total - Changing start date

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] ) )
    )
)

View solution in original post

Microsoft v-jiascu-msft
Microsoft

Re: Cumulative Total - Changing start date

Hi @JulietteGSA,

 

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

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,831)