Forum Discussion

scorrera's avatar
scorrera
Regular Visitor
2 years ago
Solved

DAX Help

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

Need Help
  • K0da's avatar
    K0da
    2 years ago

    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.

1 Reply

  • K0da's avatar
    K0da
    New Member
    2 years ago

    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.