topic Re: DAX To Calculate the Difference between two months of a Measure in DAX Commands and Tips

DAX To Calculate the Difference between two months of a Measure

Anonymous — Thu, 11 Jul 2019 14:50:53 GMT

I have a number of similiar measures, e.g.

Risk Count = CALCULATE(
                COUNT('Flags Data'[Risk]),
                'Flags Data'[Risk] = "0"
)

I have used these in a matrix against some name and and showing Months as a Column Value, there are 2 months.

I wish to show the difference between the two months but am unsure how.

I have tried the below to get the previous month count, with the intention of subtracting the two, but this just show as a blank, and also it shows up in the matrix for both months, when I just want one difference column.

Previous Month Risk Count = CALCULATE( [Risk Count],
                            PREVIOUSMONTH(DateTable[Date]))

Any advice appreciated!

Re: DAX To Calculate the Difference between two months of a Measure

parry2k — Thu, 11 Jul 2019 15:16:12 GMT

Anonymous seems like you have date dimension in your model which is good. From which table you are showing column in your matrix? Are you showing month from DateTable?

Re: DAX To Calculate the Difference between two months of a Measure

Anonymous — Thu, 11 Jul 2019 15:20:14 GMT

Thx!

That was the issue! I was using the Date field in my Fact Table!

Re: DAX To Calculate the Difference between two months of a Measure

Anonymous — Thu, 11 Jul 2019 15:26:59 GMT

However, this still isn't quite what I want.

I created a measure for the difference, using the measure stated above.

Risk Count Diff = [Risk Count] - [Previous Month Risk Count]

When I bring that into my table this gives a column for both my selected months, but I want to only show the later month.

So Matrix is show these Columns

May                                       June   
Risk Count     Risk Count Difference      Risk Count     Risk Count Difference

But I want to see

May            June   
Risk Count     Risk Count     Risk Count Difference

Any ideas?

Re: DAX To Calculate the Difference between two months of a Measure

parry2k — Thu, 11 Jul 2019 15:31:21 GMT

Anonymous I dont htink you can do that but instead, you drop all 3 measures on value

- Risk Count

- Previous Month Risk Count

- Risk Difference

and filter on most recent month.

This way you will only see June month on the header with 3 measures. Previous month implied it is May, Although if you want both the months on header, you cannot do it with matrix. Matrix is going to show all the values for each column (month)

Re: DAX To Calculate the Difference between two months of a Measure

Anonymous — Thu, 11 Jul 2019 15:33:52 GMT

Thats a shame, I can't do that as I have other measures in the table that I need to see for May.

Re: DAX To Calculate the Difference between two months of a Measure

parry2k — Thu, 11 Jul 2019 15:37:32 GMT

Anonymous you can be smart with your Risk Difference measure to return blank value in case of May and in that case you will not see value for May, although the header will be there whcih you can minimize and turn off auto width.

Re: DAX To Calculate the Difference between two months of a Measure

parry2k — Thu, 11 Jul 2019 15:37:51 GMT

There is no elegant soution, just have to use some tricks.