Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I searched for this and couldn't find any similar to my situation. I'm tracking a competitors price for all similar products every day as well as our own prices. Trying to come up with a calculated column where I can determined the whether there is a change in price difference between our price and the competitor price each day
e.g. for one SKU
Date Our price comp price difference change in difference DTD
01/01/17 17 18 1 1 (0 to 1)
02/01/17 17 18 1 0 (1 to 1)
03/01/17 17 17 0 1 (0 to 1)
I want to be able to check everyday using the change in price difference DTD as a filter/slicer all the products where the competitor price have changed.
So this calculated column should show the change in difference based on current data at the row level and also filtered for the the SKU.
Cheers,
Andrew
Solved! Go to Solution.
HI @Pentanol
Andrew
Try this solution
First Add a calculated Column which will RANK the dates for each SKU
Date RANK = RANKX ( FILTER ( ALL ( Table1 ), Table1[SKU] = EARLIER ( Table1[SKU] ) ), Table1[Date], , ASC, DENSE )
Now you can get the Change in Difference using this Calculated Column
Change_In_Difference = VAR previousDayDifference = CALCULATE ( SUM ( Table1[Comp Price] ) - SUM ( Table1[Our Price] ), FILTER ( ALLEXCEPT ( Table1, Table1[SKU] ), Table1[Date RANK] = EARLIER ( Table1[Date RANK] ) - 1 ) ) VAR CurrentDayDifference = Table1[Comp Price] - Table1[Our Price] RETURN CurrentDayDifference - previousdayDifference
See the pic below.I just added another SKU to your sample data
HI @Pentanol
Andrew
Try this solution
First Add a calculated Column which will RANK the dates for each SKU
Date RANK = RANKX ( FILTER ( ALL ( Table1 ), Table1[SKU] = EARLIER ( Table1[SKU] ) ), Table1[Date], , ASC, DENSE )
Now you can get the Change in Difference using this Calculated Column
Change_In_Difference = VAR previousDayDifference = CALCULATE ( SUM ( Table1[Comp Price] ) - SUM ( Table1[Our Price] ), FILTER ( ALLEXCEPT ( Table1, Table1[SKU] ), Table1[Date RANK] = EARLIER ( Table1[Date RANK] ) - 1 ) ) VAR CurrentDayDifference = Table1[Comp Price] - Table1[Our Price] RETURN CurrentDayDifference - previousdayDifference
See the pic below.I just added another SKU to your sample data
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |