DAX Help

[scorrera]

Hello,

I have a table that shows spending for different center-accounts across time. It looks like this:

The Actual Spend column is a calculated column. I also calculated as a measure.

I would like to have a visual that shows the top 20 center-accounts with the biggest change in spend from month to month. So center-account 1234 it would take 25-90= -65 and if that was one of the top center-accounts for change it would list it in a visual with what the change was. Ideally I'd like to be able to pick a past month, say from 5 months ago, and have this still work but I would take it if it only worked for the latest month and its previous month. I would also like for it to show top 20 biggest change positive and negative.

Thanks in advance,

Steve

[K0da · Accepted solution]

Hi,

For what i understood you need the difference from last day of the month and first day of the month, for each month, for the column [Actual Spending].

So, i created two colums, Month_Year, and Amplitude, it goes like this

Month_Year = MONTH(Plan1[Calendar Date]) & "/" & YEAR(Plan1[Calendar Date])

Amplitude = 
VAR SPENDING_LAST_DAY = CALCULATE(
    SUM(Plan1[Actual Spending]),
    FILTER(
    ALL(Plan1),
        EARLIER(Plan1[Center-Account]) = Plan1[Center-Account] && EARLIER(Plan1[Month_Year]) = Plan1[Month_Year] && ENDOFMONTH(Plan1[Calendar Date]) = Plan1[Calendar Date]
    )
)

VAR SPENDING_FIRST_DAY = CALCULATE(
    SUM(Plan1[Actual Spending]),
    FILTER(
    ALL(Plan1),
        EARLIER(Plan1[Center-Account]) = Plan1[Center-Account] && EARLIER(Plan1[Month_Year]) = Plan1[Month_Year] && STARTOFMONTH(Plan1[Calendar Date]) = Plan1[Calendar Date]
    )
)

RETURN SPENDING_LAST_DAY-SPENDING_FIRST_DAY

The result 

with this column you can do all of the other things like a top 20, and a calendar filter would work in any interval you want.

Theres probably a cleaner way to do it but, i hope that helps.