Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Cumulative total between two selected dates

Hello

You would need to calculate the total accumulated by months and years of a value but that is between two segmented dates (start and end) and that when the year changes it starts from 0.

I have that to this extent and it works well.

Cumulative measure =
CALCULATE(SUM(Sheet1[Value]),FILTER(ALL(Hoja1),Hoja1[Fecha_A]<=MAX(Hoja1[Fecha_A]) && Sheet1[Year]=MAX(Sheet1[Year])))
This he does well.
kikejnt89_1-1648464832399.png

But I want to put additionally in the measure and without creating a segmentation that makes me the calculation between two specific dates. A specific start date and a specific end date in the same column (Date A).

Example pbix attachment.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Syndicate_Admin ;

    You could try it.

    Cumulative measure =
IF (
    ISINSCOPE ( 'Sheet1'[Year] ),
    CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER (
            ALL ( Hoja1 ),
            Hoja1[Fecha_A] <= MAX ( Hoja1[Fecha_A] )
                && Sheet1[Year] = MAX ( Sheet1[Year] )
        )
    ),
    SUM ( Sheet1[Value] )
)


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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Syndicate_Admin , Try using allselected

     

    CALCULATE(SUM(Sheet1[Value]),FILTER(ALLselected(Hoja1),Hoja1[Fecha_A]<=MAX(Hoja1[Fecha_A]) && Sheet1[Year]=MAX(Sheet1[Year])))

     

    Also, you can force like this example

    Cumm Sales =
    var _max = maxx(allselected(Date),Date[Date])
    var _min = mainx(allselected(Date),Date[Date])
    return

    CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date]) && 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

    • JoaoEccel's avatar
      JoaoEccel
      Regular Visitor
      2 years ago

      Using allselected() worked for me. Thank you so much!

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Syndicate_Admin ;

    You could try it.

    Cumulative measure =
IF (
    ISINSCOPE ( 'Sheet1'[Year] ),
    CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER (
            ALL ( Hoja1 ),
            Hoja1[Fecha_A] <= MAX ( Hoja1[Fecha_A] )
                && Sheet1[Year] = MAX ( Sheet1[Year] )
        )
    ),
    SUM ( Sheet1[Value] )
)


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