cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.
4 REPLIES 4
Frequent Visitor

Hi @Shravan133 and @v-fenling-msft ,

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.

Community Support

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.

Community Support

Hi, @Shravan133

And I would like to share some additional solutions below.

Hi, @Swati20

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.

Solution Supplier

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.