Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a table that contains the last 5 tvalues for each Prod_ID ordered by date desc.
I need help with a measure that will calculate the value at a row minus the most recent value for each Prod_ID.
End result should look like this;
Thanks in Advance
Solved! Go to Solution.
Hi @Anonymous
You may try to create a measure like below:
Measure = VAR Recent_date = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[tValue] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ), 'Table'[Date] = Recent_date ) ) - CALCULATE ( SUM ( 'Table'[tValue] ) )
Regards,
Hi @Anonymous
You may create a calculated column with below formula:
Column = VAR Recent_date = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[tValue] ), FILTER ( 'Table', 'Table'[Date] = Recent_date ) ) - 'Table'[tValue]
Regards,
Hi @v-cherch-msft ,
Thanks for your reply, it's great but something is lacking.
My table has more than 2000 rows with some Prod_ID having the same latest dates.
What the formula does is for those products, it will sum all their tValues whereas I need it to calculate for each Distinct Prod_ID.
Thanks
Hi @Anonymous
You may try to create a measure like below:
Measure = VAR Recent_date = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ) ) RETURN CALCULATE ( SUM ( 'Table'[tValue] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ), 'Table'[Date] = Recent_date ) ) - CALCULATE ( SUM ( 'Table'[tValue] ) )
Regards,
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |