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

Greg_Deckler 

 

Actually there is no formula, I did this

 

 

but I also tryied create a measure with a simple DISTINCTCOUNT(Amount_Id) and use this measure on CALCULATE but it didn't work.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

Anonymous Right, so implicit measures ( what you are doing ) work for visuals but have no bearing on measure formulas. Any time you reference a column in a measure, you need to wrap an aggregation around it like MAX, MIN, COUNT, SUM, DISTINCTCOUNT, etc. I suspect the issue is context and you need to do somethign like this:

CALCULATE(DISTINCTCOUNT([Amount_ID]), ALLSELECTED('Consulta2'), DATEADD(Consulta2[year_month], -1, MONTH))

That said, I would avoid DAX TI functions as they are a pain in numerous ways. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008