Help Creating a Cost Savings Visualization

[Anonymous]

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	102	10

What I would like to do is create a visualization that shows that Cost Center 101 saved $0 in March and saved $5 in April, and Cost Center 102 saved $2 in March, then $13 in April (compared to if the February Costs stayed the same). How can I create a list visualization that does this, with slicers that allow for choosing the Cost Center and Month at the top? Thank you in advance for anyone who helps; I've been racking my brain for days trying to figure this out but can't! 

[v-easonf-msft]

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]

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.  

[Anonymous]

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

[Anonymous · Accepted solution]

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! 

[amitchandak]

@Anonymous , in case you have date , Use time intelligence and date calendar.

If needed add + 0 to MTD measure

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak, I'm a total noob here; is the example you came up with supposed to all be one measure or each line item a different measure? When I try to put it in as one measure, I get a syntax error. Thanks for your help!