Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate the monthly variation using DISTINCTCOUNT

Hello all!   I have the following table: Year/Month Amount_Id 2021-06 5000 2021-07 4000 2021-08 6000 2021-09 5500   I need to create a third column with the monthly variat...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    I create a table as you mentioned.

    Then I create a calculated column and here is the DAX code.

    Column = 
VAR _previousmonth =
    CALCULATE (
        MAX ( 'Table'[Year/Month] ),
        FILTER ( 'Table', 'Table'[Year/Month] < EARLIER ( 'Table'[Year/Month] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Amount_Id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year/Month] = _previousmonth )
    )

    Finally I create a calculated column and get what you want.

    Column 2 = 
IF (
    'Table'[Column] = 0,
    BLANK (),
    ( 'Table'[Amount_Id] - 'Table'[Column] ) / 'Table'[Column]
)

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Column = 
VAR _previousmonth =
    CALCULATE (
        MAX ( 'Table'[Year/Month] ),
        FILTER ( 'Table', 'Table'[Year/Month] < EARLIER ( 'Table'[Year/Month] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Amount_Id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year/Month] = _previousmonth )
    )

Finally I create a calculated column and get what you want.

Column 2 = 
IF (
    'Table'[Column] = 0,
    BLANK (),
    ( 'Table'[Amount_Id] - 'Table'[Column] ) / 'Table'[Column]
)

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

Is it possible include groups into the dax formula?

For example, now i have the following table:

       GROUP A      GROUP B
Year/Monthamount_id% var.amount_id% var.
2021-065000 200 
2021-074000 150 
2021-086000 400 
2021-095500 320 

 

This formula worked well but it doesn't consider the groups A and B. The output is the same for both groups.

For exemple:

     GROUP A    GROUP B
Year/Monthamount_id% var.amount_id% var.
2021-065000 200 
2021-07400050001505000
2021-08600040004004000
2021-09550060003206000

 

 

I used your formula

 

Column = 
VAR _previousmonth =
    CALCULATE (
        MAX ( 'Table'[Year/Month] ),
        FILTER ( 'Table', 'Table'[Year/Month] < EARLIER ( 'Table'[Year/Month] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Amount_Id] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year/Month] = _previousmonth )
    )