Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Largest Change from Previous month HELP

Hi team,

 

I'm trying to create a visual that shows the top 5 variables in a specific column that experienced the largest change since the previous month. So as we add data on a monthly basis, I would like to show the largest 'movers' each time we refresh the data (on a monthly basis). The variables are string values. 

 

My inputs are the following measures I've created:

 

Current Month = CALCULATE((COUNTROWS( 'CLIENT DATASET')))
Previous month Count  CALCULATE( COUNTROWS('CLIENT DATASET') , PREVIOUSMONTH('CLIENT DATASET'[Time of Breach]))
Difference = [Current Month] - [Previous month Count]
 
When I apply the final Measure (Difference) to the variable in the table I want to analyse the monthly change (a column with string values, not numbers, but thats where i presume the [Current Month] measure comes in) and create a table visual out of it,  I get the exact same output if I were to create a table visual with the variable against count per variable. 
 
Please help in informing me how im going about this problem in the wrong manner. 
 
cheers
 
 
 
 
change
DAX
Visual

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI there,

       

      Thank you for offering a solution.

       

      I am trying to get the Top N relative changes , not absolute changes. So both your solution and my current one is showing absolute changes. E.g. X1 = 1000 , X2 = 3000 ; A1 = 200 A2 = 1000 . The larger relative change from month 1 to 2 happened to variable A not Variable X, but the current solution would show X above A in a table for instance. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Just to add I have created a new measure which calculates the total for each column but only for the latest month: 

         

        Current Month  = TOTALMTD(COUNTROWS('CLIENT DATASET'), 'CLIENT DATASET'[Dates] )
         
        I have tried to create a similar measure for the previous month, but the output on any visual is always blank:
         
        Previous month Count = CALCULATE( COUNTROWS('CLIENT DATASET') , PREVIOUSMONTH('CLIENT DATASET'[Dates]))
         
        Any reasons why this is?