Cumulative not working correctly

[Antmkjr]

https://drive.google.com/file/d/1pWvFXrZcE9s8QAq9dBjkaBSEUlJMJCRD/view?usp=sharing

I am trying to get the cumulative of EW , and it is not working.

Why is this happening ,I understand I have to force the context, but not very clear on that part.
Can some one explain?

[JW_van_Holst]

You don't need to force the context transition, you have to break it!
First create a calculated column:

BreakContextTransitionTable = 
VAR __YMTable = SUMMARIZE(ALL('Date'), 'Date'[MonthnYear])
VAR __YMTableEW = ADDCOLUMNS(__YMTable, "@EW", [EW])
RETURN
__YMTableEW

Then add this measure:

**bleep** EW = 
VAR __filter = FILTER(BreakContextTransitionTable, BreakContextTransitionTable[MonthnYear] <= MAX('Date'[MonthnYear]))
RETURN
SUMX(__filter, BreakContextTransitionTable[@EW])

Giving this result:

Of course you can break the context transition by rewriting the EW measure (removing all Calculate())

[Antmkjr]

Could you please attach pbix as well?

"First create a calculated column:" - You mean calculated table? Is there a way without creating calculated table?

"Of course you can break the context transition by rewriting the EW measure (removing all Calculate())" - I didnt understand this, would you be so kind to explain this a bit?

[JW_van_Holst]

Sorry, calculated table. 

Yes, theoretically it should be possible to do this with a measure that doesn’t use Calculate (that causes context transition). How ever due to the conditions in your measures on cumulative measures EW is non additive for all granularities and non additive for cumulative measures. It will be a great ask to solve it this way.

https://drive.google.com/file/d/1vblTL7cTzKQV1pBQRqG4uMH3pdXLUk9p/view?usp=sharing 

[Antmkjr]

I am sorry for asking again, but i always dont get this. So just want to be clear this time.

"How ever due to the conditions in your measures on cumulative measures EW is non additive for all granularities and non additive for cumulative measures"--Would you /someone be so kind to explain this a bit

[JW_van_Holst]

You don't need to force the context transition, you have to break it!
First create a calculated column:

BreakContextTransitionTable = 
VAR __YMTable = SUMMARIZE(ALL('Date'), 'Date'[MonthnYear])
VAR __YMTableEW = ADDCOLUMNS(__YMTable, "@EW", [EW])
RETURN
__YMTableEW

Then add this measure:

EW = 
VAR __filter = FILTER(BreakContextTransitionTable, BreakContextTransitionTable[MonthnYear] <= MAX('Date'[MonthnYear]))
RETURN
SUMX(__filter, BreakContextTransitionTable[@EW])

Giving this result:

Of course you can break the context transition by rewriting the EW measure (removing all Calculate())

[amitchandak]

@Antmkjr , have you tried measures like these with or without help from date table

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))

also you can try quick measure

[Antmkjr]

Of course  i tried this 

Cumulative EW =

CALCULATE([EW], FILTER (

ALLSELECTED ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] ) ))

But not working.

[Antmkjr]

@amitchandak Could you please help?