DAX for Azure Cost Table

[Anonymous]

Hi Everyone,

I have a table visual which looks like this.

resourceGroup	Jan	Feb	Mar	Apr	May	Jun	Jul
RG1	1165	789	63	736	232	115	900
RG2	1389	1584	721	1952	1517	137	114
RG3	269	506	1657	937	199	1149	1813
RG4	1377	1827	360	1568	652	226	1618
RG5	1712	158	1189	503	292	1128	991
RG6	1299	1959	808	1489	1188	82	675
RG7	1466	1402	1596	586	329	93	1671
RG8	550	1193	18	861	1387	1738	1742
RG9	370	1022	1548	1400	532	1030	64
RG10	196	1045	540	370	1165	1761	1089

What I would like to do is -

Have a slicer for choosing a specific month (this is easily done).

Whenever a specific month is being chosen (from the slicer), the chosen month and the previous month relative to it have to be displayed, along with their costs.

And then display the cost difference between the two via a DAX or whatever you think is better/easier.

Example:

Chosen month in the slicer: July

Output:

resourceGroup	Jun	Jul	Diff
RG1	1206	208	-998
RG2	874	1898	1024
RG3	347	847	500
RG4	1245	1084	-161
RG5	721	818	97
RG6	309	838	529
RG7	711	278	-433
RG8	1893	632	-1261
RG9	1291	1377	86
RG10	1736	1894	158

I'm still new to Power BI and any kind of help will be highly appreciated. 

Thanks!

[amitchandak · Accepted solution]

@Anonymous , do you have date in you original data. if so you can use time intelligence and date table

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

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

refer: Power BI — MTD  - check without time intelligence option
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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]

Thanks, @amitchandak! That worked perfect!

The following measures display same values as output. Does it matter if I use any of them? Or rather, what are the different cases to use them properly?

- last MTD Sales
- last month Sales

- last MTD (complete) Sales

Will find time to study better time intelligence and date table. Really glad there are very active people in this community. 🙂