Tricky Cumulative Sum when using a static Year, Need help with measure

[Anonymous]

I have a requirement from the firm, which is to create a cummalative sum of values based on a target year. So the financial year we are using as a base target is 2019 which goes from 2018/06- 2019/05. The calculation needs to look like this if i drop Month-Year into a table:

I have a requirement from the firm, which is to create a cummalative sum of values based on a target year. So the financial year we are using as a base target is 2019 which goes from 2018/06- 2019/05. The calculation needs to look like this if i drop Month-Year into a table:

Date	New Measure Month Value	Cummalative
06- 2018	1000	1000
07 - 2018	150	1150
08 - 2018	125	1275
......	..	...
07 - 2021	150 (same as 2019 FY)	100,000
08 - 2021	125 (same as 2019 FY)	100,125
09 - 2021	....	...

My current working query looks like this:

Total Emissions Flights 2019 Month 2 =

var MinDate = CALCULATE(Min(Periods[Period_Year]),FILTER(ALLSELECTED(Periods), MIN(Periods[Period_Year]) = Periods[Period_Year]))

var MaxDate = CALCULATE(Max(Periods[Period_Year]),FILTER(ALLSELECTED(Periods), Max(Periods[Period_Year]) = Periods[Period_Year]))

var Calc = CALCULATE(SUM('Tandem Flights'[Emissions no RF (tCO2e)]),FILTER(ALL( Periods ), Periods[Period_Year] = 2019))

var Maxcalc = CALCULATE(SUM('Tandem Flights'[Emissions no RF (tCO2e)]),FILTER(ALL ( Periods ), Periods[Period_monthno] <= MAX (Periods[Period_monthno]) && Periods[Period_Year] = 2019))

return

Calc * (MaxDate - 2019) + Maxcalc

It works only if you track it right on the start of the period year 2019. I was wondering if it is possible to simplify this query, so all i have to do is run a calculate that uses the Rows Month and swaps out the rows period Year with 2019 in order to get the correct amount using the relative month?

[amitchandak]

@Anonymous , Try like this example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date]) && date[Year] = 2019))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date]) && year(date[Date]) = 2019))

[Anonymous]

Hi None of those measures worked. Thanks for trying.

[Anonymous]

Hi @Anonymous,

Can you please share some dummy data with a similar data structure then we can test to coding formula on it?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng