DAX calculate difference between current and previous month - show values for all categories

Werdinkaa — Wed, 29 Apr 2020 12:24:00 GMT

Hi,

I have a model in Power pivot one with so called Keys which is related to my main data (Category,Key,Date, Value)

I have a slicer with months and pivot table which shows Key and calculated values from main table.

I need to show difference between selected month and previous month and I am using those measures

Total = sum(Main[Value])

PreviousMonthTotal = calculate([Total], previousmonth(Main[Date])

It works nice however I need to calculate difference between it and if specific key didnt exist in data for current month it should show previous one and the other way, if its not in current then still show key but with blank.

Unfortunately it shows only keys which exist in month selected on slicer.

if in pivot table I only show grouped values by category results are correct, but when I go to maximum granularity (key) it doesnt show me exact resutlts.

How can I force it to show in pivot table all keys from those two months?

Re: DAX calculate difference between current and previous month - show values for all categories

v-lid-msft — Thu, 07 May 2020 00:44:31 GMT

Hi Werdinkaa ,

We can try to create a seprate dim table first. (keep no relationship with the main table)

CategoryTable = Distinct('Main'[Category])

Then replace the measure total as following to meet your requirement:

Total = CALCULATE ( SUM ( Main[Value] ), Main[Category] IN DISTINCT ( CategoryTable[Category] ) )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to OneDrive For Business and share the link here.

Best regards,

topic DAX calculate difference between current and previous month - show values for all categories in DAX Commands and Tips

DAX calculate difference between current and previous month - show values for all categories

Re: DAX calculate difference between current and previous month - show values for all categories