Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help Creating a Cost Savings Visualization

Hello, I'm trying to make a visualization from the following table: Month Cost Center Charge Feb 101 $20. Feb 102 $23 Mar 101 20 Mar 102 25 April 101 15 April 10...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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! 
v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

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:

 

pbix attached 

 

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-easonf-msft 

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.