cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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:

 

 

DateNew Measure Month Value Cummalative

06- 2018      

10001000

07 - 2018

150

1150

08 - 2018

1251275

......

.....
07 - 2021150 (same as 2019 FY)100,000
08 - 2021125 (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?
 
3 REPLIES 3
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Anonymous
Not applicable

Hi None of those measures worked. Thanks for trying.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors