Forum Discussion
Help Creating a Cost Savings Visualization
YAY!!! I had a coworker who helped me solve this one:
February Cost = IF([Total Costs]=BLANK(),BLANK(),SUMX(VALUES(Date),CALCULATE([Total Costs],FILTER(ALL(Date),Date[Month]=DATE(2020,02,01)))))
!!! Thank you again for trying to help out, though amit and v-easonof!
Hi , Anonymous
Try to create calculate columns as below:
Month_num = SWITCH('Table'[Month],"Feb",2,"Mar",3,"April",4 //.....other months
)last_month charge = CALCULATE(MAX('Table'[Charge]),'Table','Table'[Month_num]<EARLIER('Table'[Month_num]),'Table'[Cost Center]=EARLIER('Table'[Cost Center]))Save =
var save= 'Table'[last_month charge]-'Table'[Charge]
return IF(ISBLANK('Table'[last_month charge]),BLANK(),save)The result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this is SO close. The problem is I'm trying to compare everything back to February's costs, not the previous month. So i took your last_month charge DAX and change it to:
last_month charge = CALCULATE(MAX('Table'[Charge]),'Table','Table'[Month_num]=date(2020,02,01)),'Table'[Cost Center]=EARLIER('Table'[Cost Center]))and getting a wonky result. Thoughts? Thanks for your help! Like i said, this one is really, really close.
v-easonf-msft Essentially, a
February Costs = Calculate('table'[costs],'table'[month]=date(2020,02,01))DAX is returning the correct result, but it's only giving me the value for February and I want it to translate down to the rest of the months for comparison/forecasting purposes, if that makes sense.
YAY!!! I had a coworker who helped me solve this one:
February Cost = IF([Total Costs]=BLANK(),BLANK(),SUMX(VALUES(Date),CALCULATE([Total Costs],FILTER(ALL(Date),Date[Month]=DATE(2020,02,01)))))
!!! Thank you again for trying to help out, though amit and v-easonof!