calculating cumulative sum of a measure that uses offset function to get the previous row value.

[Swati20]

Hi, 
I'm trying to calculate the cummulative sum(say Measure2) of an already created measure (say Measure1) that is using "Offset" function to get the previous week value for the current week.
In the Cummulative sum, I've to add previous week sum calculated with the current week value.

The Problem is when I calculate the cummulative sum( [Measure2] ), it's only taking the current week value ([Measure]) and ignoring the previous week value ([Measure1])
Please look at the measure for current week value calculation:

Measure = Sum('Table1'[Abc])

To get the previous week value I've created another measure using "Offset" function:

Measure1 = 

CALCULATE (

        [Measure] ,

        OFFSET( -1, 

            ORDERBY('Calendar'[QTR Week], ASC)) 
)

And the measure for calculating cummulative sum is as follows:
Measure2 = 

CALCULATE(

   [Measure] - [Measure1],

    FILTER(

        ALLSELECTED('Calendar'[QTR Week]),

        ISONORAFTER('Calendar'[QTR Week], MAX('Calendar'[QTR Week]), DESC)

    )

)

Your help would be appriciated as I'm struggling to find any solution for this since a long time now.

[Swati20]

Hi @Shravan133 and @Anonymous ,
Thanks for your response.

I tried the dax shared by both of you. However, I'm still not getting the expected output as its still taking only the current value and ignoring the previous value. 

[Anonymous]

Hi, @Swati20 

Could you please provide some sample data in text format (excluding sensitive data) with expected results with backend logic and special examples?

Simplified pbix files would be appreciated. This will help us to quickly pinpoint your problem and resolve it.

We will do our best to help you solve the problem you are experiencing.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @Shravan133 

thanks for your concern about this issue. 

Your answer is excellent! I agree with your calculations. 

And I would like to share some additional solutions below. 

Hi, @Swati20 

I am glad to help you. 

According to your description, you want to calculate cumulative sum of a measure that uses offset function to get the previous row value?

If I understand you correctly, then you can refer to my solution. 

I think the reason for the problem with your calculations may be that the way ISONORAFTER function is used may result in only the current week and subsequent weeks being taken into account, rather than accumulating the previous weeks. 

And you can refer to my formula: 

Measure2 =
VAR CSTable =
    SUMMARIZE (
        'Calendar',
        'Calendar'[QTR Week],
        "CurrentWeekValue", [Measure],
        "PreviousWeekValue", CALCULATE ( [Measure], OFFSET ( -1, ORDERBY ( 'Calendar'[QTR Week], ASC ) ) )
    )
RETURN
    SUMX (
        FILTER ( CSTable, 'Calendar'[QTR Week] <= MAX ( 'Calendar'[QTR Week] ) ),
        [CurrentWeekValue] - [PreviousWeekValue]
    )

I hope this helps solve your problem. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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

[Shravan133]

try this: Measure2 =
CALCULATE(
SUMX(
FILTER(
ALLSELECTED('Calendar'[QTR Week]),
'Calendar'[QTR Week] <= MAX('Calendar'[QTR Week])
),
[Measure] - CALCULATE(
[Measure],
OFFSET(
-1,
ORDERBY('Calendar'[QTR Week], ASC)
)
)
)
)

Or 

Measure2 =
CALCULATE(
SUMX(
FILTER(
ALL('Calendar'),
'Calendar'[QTR Week] <= MAX('Calendar'[QTR Week])
),
[Measure] - CALCULATE(
[Measure],
OFFSET(-1, ORDERBY('Calendar'[QTR Week], ASC))
)
),
REMOVEFILTERS('Calendar')
)