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?
