Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Trying to write a measure to calculate the cumulative difference between months.
For example, I'm working with the data below.
Yearmonth Sales
2020/01/01 10
2020/02/01 15
2020/03/01 14
2020/04/01 11
Total 50
Then each month difference is 5, -1, -3 and cumulative total difference is 1 (5+ -1 + -2).
Can you please help me to figure out how to get the total (i.e. 1)?
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file for you(see attachment) base on your requirement, please check whether that is what you want.
Difference =
var _cursales=MAX('Table'[Sales])
var _curdate=MAX('Table'[Yearmonth])
var _predate=CALCULATE(MAX('Table'[Yearmonth]),FILTER(ALL('Table'),'Table'[Yearmonth]<_curdate))
var _presales=CALCULATE(MAX('Table'[Sales]),FILTER(ALL('Table'),'Table'[Yearmonth]=_predate))
return
if(ISBLANK(_presales),BLANK(),_cursales-_presales)
Culmulative difference = SUMX(FILTER(ALL('Table'),'Table'[Yearmonth]<=SELECTEDVALUE('Table'[Yearmonth])),[Difference])
Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Hi @Anonymous
You can try some thing similar to below:
Thanks
Manoj
Thank you for the help! I created two measures and it gives me a difference for each month. However, I can't seem to add the difference to get total 1.
Below is a bar chart using the measure.
I would like a cumulated difference bar chart. For example, if I select 04/01/2020, I would like the bar chart to show 1. But it's showing -3, just a difference between March and April.
Can you please help?
Hi @Anonymous ,
I created a sample pbix file for you(see attachment) base on your requirement, please check whether that is what you want.
Difference =
var _cursales=MAX('Table'[Sales])
var _curdate=MAX('Table'[Yearmonth])
var _predate=CALCULATE(MAX('Table'[Yearmonth]),FILTER(ALL('Table'),'Table'[Yearmonth]<_curdate))
var _presales=CALCULATE(MAX('Table'[Sales]),FILTER(ALL('Table'),'Table'[Yearmonth]=_predate))
return
if(ISBLANK(_presales),BLANK(),_cursales-_presales)
Culmulative difference = SUMX(FILTER(ALL('Table'),'Table'[Yearmonth]<=SELECTEDVALUE('Table'[Yearmonth])),[Difference])
Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |