Forum Discussion
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 | 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!
6 Replies
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.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!
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.
