DAX for Azure Cost Table

Question

Hi Everyone,&nbsp;I have a table visual which looks like this.resourceGroupJanFebMarAprMayJunJulRG1116578963736232115900RG21389158472119521517137114RG3269506165793719911491813RG41377182736015686522261618RG5171215811895032921128991RG6129919598081489118882675RG7146614021596586329931671RG8550119318861138717381742RG9370102215481400532103064RG101961045540370116517611089&nbsp;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.&nbsp;Example:Chosen month in the slicer: JulyOutput:resourceGroupJunJulDiffRG11206208-998RG287418981024RG3347847500RG412451084-161RG572181897RG6309838529RG7711278-433RG81893632-1261RG91291137786RG1017361894158&nbsp;I'm still new to Power BI and any kind of help will be highly appreciated.&nbsp;&nbsp;Thanks!

amitchandak · Accepted Answer

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])
&nbsp;
refer: Power BI — MTD&nbsp; - check without time intelligence optionhttps://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
&nbsp;
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-bihttps://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#M184Appreciate your Kudos.

Forum Discussion

DAX for Azure Cost Table

2 Replies

Related content

Azure Map Visual - additional costs

Azure Data Fabric Warehouse cost

Azure Cost Management scheduled refresh

Fabric & Azure Data User Group Program Requirements & Benefits

Cost allocation

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

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