Forum Discussion
Calculate the monthly variation using DISTINCTCOUNT
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.
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.
Hi Anonymous
Is it possible include groups into the dax formula?
For example, now i have the following table:
GROUP A GROUP B Year/Month amount_id % var. amount_id % var. 2021-06 5000 200 2021-07 4000 150 2021-08 6000 400 2021-09 5500 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/Month amount_id % var. amount_id % var. 2021-06 5000 200 2021-07 4000 5000 150 5000 2021-08 6000 4000 400 4000 2021-09 5500 6000 320 6000 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 ) )