DAX for Azure Cost Table - part 2

[Anonymous]

Hi everyone,

I have a previous thread/message from the below link.

https://community.powerbi.com/t5/Desktop/DAX-for-Azure-Cost-Table/m-p/1296381#M564798

Details are as follows:

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

The solution I got was using time intelligence (MTD and PREVIOUSMONTH functions) and that requires a separate date table. My concern right now is, what would be the alternative if I have no (or cannot create) date table? I am trying to recreate my visualizations from an existing data set which I have no rights to modify. 

Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I'd like to suggest you do unpivot columns on your month fields to convert them to attribute and value fields to calculate.

Unpivot columns (Power Query) 

Then you can create a sorting table to setting custom sort order of month fields and duplicate it as a selector table to use on the slicer. (sort table link to raw table month fields, selector table not has any relationship to other tables)

Sorting =
SELECTCOLUMNS (
    GENERATESERIES ( 1, 12, 1 ),
    "Index", [Value],
    "Month", FORMAT ( DATEVALUE ( [Value] & "/1" ), "mmm" )
)

Selector = Sorting

Custom Sorting in Power BI 

After these steps, you can design matrix visual with raw table resource Group, sort order table month, and write a measure to interact with slicer and calculated the selected diff. (name column subtotal as diff)

result = 
VAR selected =
    CALCULATE ( MAX ( 'Selector'[Index] ), ALLSELECTED ( 'Selector' ) )
VAR list =
    CALCULATETABLE (
        VALUES ( Sorting[Month] ),
        FILTER ( ALL ( Sorting ), [Index] IN { selected - 1, selected } )
    )
RETURN
    IF (
        ISINSCOPE ( Sorting[Month] ),
        IF ( SELECTEDVALUE ( Sorting[Month] ) IN list, SUM ( Test[Sales] ) ),
        CALCULATE (
            CALCULATE ( SUM ( Test[Sales] ), Sorting[Index] = selected - 1 )
                - CALCULATE ( SUM ( Test[Sales] ), Sorting[Index] = selected ),
            ALLSELECTED ( Test ),
            VALUES ( Test[resourceGroup] )
        )
    )

BTW, I also attached the sample pbix file below, you can check it if you still confused about how to do these.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Assume you need to display jun, jul as the legend and diff a column. Matrix does not support Hybrid display. so You need opt for some solution to do it.

2. When you select a one-month slicer and do not use time intelligence, The measure which you build will give you 2 months you will get rolling data. to avoid that and two display month you again need 2 tables

https://www.youtube.com/watch?v=duMSovyosXE

You can have MTD measure and diff measure and use that in the matrix with the month as legend or this month /last month and diff.  I thing time intelligence is best suited for your case

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))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

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

I think time intelligence is best solution