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.
Community ChampionAnonymous So what is the formula for you Amount_id measure?
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
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