Forum Discussion
Minus 2 measure previous value
Dear All,
Could you help me show how to minus between 2 column previous Measure in PowerBI.
Source:
Destination desire
M.ExpectedPre = M.ExpectedValue - Previous(M.Duration)
Any column above are measure type.
Many thanks.
Hi Anonymous
I update my Calculated Column and Measure in your Sample and hope this may help you.
Rank column:
C.Rank = RANKX(FILTER(DemoTracking,DemoTracking[ITEMCODE] = EARLIER(DemoTracking[ITEMCODE])),DemoTracking[TransferID],,ASC,Dense)Measure:
M.ExpectedPre = VAR _Pre = CALCULATE ( [M.ExpectedValue], FILTER ( ALL(DemoTracking) ,DemoTracking[ITEMCODE]=MAX(DemoTracking[ITEMCODE])&& DemoTracking[C.Rank] = MAX ( DemoTracking[C.Rank] ) - 1 ) ) RETURN IF ( _Pre = BLANK (), BLANK (), _Pre - [M.Duration] )Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
Due to I don't know your data model, so I have to build a sample table to have a test.
Add a Rank column in this Table.
Rank = RANKX('Table','Table'[M.To Date],,ASC,Dense)Due to M.ExpectedPre = M.ExpectedValue - Previous(M.Duration) are all measures, so build measures.
M.Duration = SUM('Table'[3])M.ExpectedValue = SUM('Table'[4])M.ExpectedPre = VAR _Pre = CALCULATE ( [M.ExpectedValue], FILTER ( ALL ( 'Table' ), 'Table'[Rank] = MAX ( 'Table'[Rank] ) - 1 ) ) RETURN IF ( _Pre = BLANK (), BLANK (), _Pre - [M.Duration] )Result:
You can download the pbix file from this link: Minus 2 measure previous value
If this reply still couldn't help you solve your problem, please show me your data model and you calculate logic of these three columns. Or you can provide me with your pbix file by you OneDrive for Business. And this may make it easier for me to understand your calculate logic.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Anonymous ,
Please focus here:
Please download link: https://lfshare-my.sharepoint.com/:u:/g/personal/khoaphan_idsmed_com/EZ0IKgyAlZFHinX3npczy2IBpxDl_uA7cMKCMtLRJkj5OA?e=qpwaR0
Many thanks RicoZho,
Hi Anonymous
I update my Calculated Column and Measure in your Sample and hope this may help you.
Rank column:
C.Rank = RANKX(FILTER(DemoTracking,DemoTracking[ITEMCODE] = EARLIER(DemoTracking[ITEMCODE])),DemoTracking[TransferID],,ASC,Dense)Measure:
M.ExpectedPre = VAR _Pre = CALCULATE ( [M.ExpectedValue], FILTER ( ALL(DemoTracking) ,DemoTracking[ITEMCODE]=MAX(DemoTracking[ITEMCODE])&& DemoTracking[C.Rank] = MAX ( DemoTracking[C.Rank] ) - 1 ) ) RETURN IF ( _Pre = BLANK (), BLANK (), _Pre - [M.Duration] )Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.