Help! - Index cumulative sum

[omelei86]

Hi 

I have 4 columns in my table 'Revenue': 

• Customer number
• Year
• Month 
• Revenue

I have created a line chart showing the cumulative revenue per month by using this measure:

Revenue **bleep** = 
CALCULATE( SUM(Revenue[revenue] ); FILTER( ALL ( Revenue[Month] ); Revenue[Month] <= MAX( Revenue[Month])))

Now I'd like to display a line chart showing the index between 2018 cumulative and 2019 cumulative. 

Hope you could help me out, 

Best, Matt

[v-shex-msft]

Hi @omelei86 ,

Can you please share some sample data for test? It will help to test and coding formulas.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

You could make 2 new calculated measures and add another filter to the Calculate formula for 'Revenue'[Year] = "2018" . . . "2019" 

Let me know how this works!

[omelei86]

Thanks @Anonymous 

I am new to DAX so could you please help me creating my new measure(s)?

Thanks..

[Anonymous]

For 2018...

2018 Revenue =
CALCULATE( SUM(Revenue[revenue] ); FILTER( ALL ( Revenue[Month] ); Revenue[Month] <= MAX( Revenue[Month])), Revenue[Year] = 2018)

For 2019...

2019 Revenue =
CALCULATE( SUM(Revenue[revenue] ); FILTER( ALL ( Revenue[Month] ); Revenue[Month] <= MAX( Revenue[Month])), Revenue[Year] = 2019)

Or as one your could do...

2019 minus 2018 Revenue = 

CALCULATE( SUM(Revenue[revenue] ); FILTER( ALL ( Revenue[Month] ); Revenue[Month] <= MAX( Revenue[Month])), Revenue[Year] = 2019) - CALCULATE( SUM(Revenue[revenue] ); FILTER( ALL ( Revenue[Month] ); Revenue[Month] <= MAX( Revenue[Month])), Revenue[Year] = 2018)