Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need two compare two value between two date.
I have a list with customers, product, price and sell date, and i need to know when the price by customer was readjusted.
Solved! Go to Solution.
Hi @gjloureiro
Try these MEASURES
Actual Price = CALCULATE ( VALUES ( TableName[PRICE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[DATE] = MAX ( TableName[DATE] ) ) )
Date of Actual Price = CALCULATE ( LASTDATE ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[CLIENT] ) )
Following MEASURES for the previous/last DATE and VALUE
lastdate = VAR last_price = [Actual Price] RETURN CALCULATE ( MAX ( TableName[DATE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[Actual Price] <> last_price ) )
lastvalue = VAR priordate = [LastDate] RETURN CALCULATE ( VALUES ( TableName[PRICE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[DATE] = priordate ) )
Hi @gjloureiro,
You can try to use below table formula to get the analysed table.
Original table:
Formula:
Table = ADDCOLUMNS ( SUMMARIZE ( 'Sample', [CLIENT], [ITEM], "Min", MIN ( 'Sample'[PRICE] ), "Max", MAX ( 'Sample'[PRICE] ) ), "Last Min Price Date", MAXX ( FILTER ( 'Sample', [CLIENT] = EARLIER ( [CLIENT] ) && [ITEM] = EARLIER ( [ITEM] ) && [PRICE] = EARLIER ( [Min] ) ), [DATE] ), "Last Max Price Date", MAXX ( FILTER ( 'Sample', [CLIENT] = EARLIER ( [CLIENT] ) && [ITEM] = EARLIER ( [ITEM] ) && [PRICE] = EARLIER ( [Max] ) ), [DATE] ) )
Regards,
Xiaoxin Sheng
Hi @gjloureiro,
You can try to use below table formula to get the analysed table.
Original table:
Formula:
Table = ADDCOLUMNS ( SUMMARIZE ( 'Sample', [CLIENT], [ITEM], "Min", MIN ( 'Sample'[PRICE] ), "Max", MAX ( 'Sample'[PRICE] ) ), "Last Min Price Date", MAXX ( FILTER ( 'Sample', [CLIENT] = EARLIER ( [CLIENT] ) && [ITEM] = EARLIER ( [ITEM] ) && [PRICE] = EARLIER ( [Min] ) ), [DATE] ), "Last Max Price Date", MAXX ( FILTER ( 'Sample', [CLIENT] = EARLIER ( [CLIENT] ) && [ITEM] = EARLIER ( [ITEM] ) && [PRICE] = EARLIER ( [Max] ) ), [DATE] ) )
Regards,
Xiaoxin Sheng
Hi @gjloureiro,
You may refer to my solution in this workbook.
Hope this helps.
Hi @gjloureiro
Try these MEASURES
Actual Price = CALCULATE ( VALUES ( TableName[PRICE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[DATE] = MAX ( TableName[DATE] ) ) )
Date of Actual Price = CALCULATE ( LASTDATE ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[CLIENT] ) )
Following MEASURES for the previous/last DATE and VALUE
lastdate = VAR last_price = [Actual Price] RETURN CALCULATE ( MAX ( TableName[DATE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[Actual Price] <> last_price ) )
lastvalue = VAR priordate = [LastDate] RETURN CALCULATE ( VALUES ( TableName[PRICE] ), FILTER ( ALLEXCEPT ( TableName, TableName[CLIENT] ), TableName[DATE] = priordate ) )
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |