Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
schrodinger
New Member

Tracking Product wise price change

schrodinger_0-1655652177205.png

This is what the data looks like. Now, the problem statement is, I need to find the dates at which each product's price changed. This data keeps on updating on daily basis. I already have a slicer on my dashboard that allows me to select a product by Item ID.  So, if i select Item id 112 and from the price change slicer, I select True, it should give me the following output.

schrodinger_1-1655652200180.png

SO basically, I want to add a new column with boolean values (True/False), which shows True, if there is a cost change as compared to the previous cost and false if it is the same as the previous cost.

 

Price Change = IF(FILTER(Sheet1, Sheet1[Item ID]=EARLIER(Sheet1[Item ID]) && Sheet1[Rank]=EARLIER(Sheet1[Rank])+1 && Sheet1[Cost]<>EARLIER(Sheet1[Cost])),TRUE,FALSE)

 

I used this, but it's giving me an error 

schrodinger_2-1655652240424.png

 

 

1 ACCEPTED SOLUTION

@Vijay_A_Verma 's solution worked like a charm aswell!  

Price Change = IF(Sheet1[Rank]=1,TRUE(),(COUNTROWS(FILTER(Sheet1, Sheet1[Item ID]=EARLIER(Sheet1[Item ID]) && Sheet1[Rank]=EARLIER(Sheet1[Rank])-1 && Sheet1[Cost]<>EARLIER(Sheet1[Cost])))>0))

View solution in original post

4 REPLIES 4
schrodinger
New Member

Thank you everyone! 

rohit_singh
Solution Sage
Solution Sage

Hi @schrodinger ,

You can try something like this :

rohit_singh_0-1655715564943.png

 

Price Change =

var _cost = max(Parts[Cost])
var _dt = max(Parts[Date])

var _prevdt =
CALCULATE(
max(Parts[Date]),
FILTER(
ALLEXCEPT(Parts, Parts[Part Number],Parts[Item ID]),
Parts[Date] < _dt
)
)

var _prevcost =
CALCULATE(
max(Parts[Cost]),
FILTER(
ALLEXCEPT(Parts, Parts[Part Number],Parts[Item ID]),
Parts[Date] = _prevdt
)
)

Return

if(_cost = _prevcost , "False", "True")
 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

@Vijay_A_Verma 's solution worked like a charm aswell!  

Price Change = IF(Sheet1[Rank]=1,TRUE(),(COUNTROWS(FILTER(Sheet1, Sheet1[Item ID]=EARLIER(Sheet1[Item ID]) && Sheet1[Rank]=EARLIER(Sheet1[Rank])-1 && Sheet1[Cost]<>EARLIER(Sheet1[Cost])))>0))

amitchandak
Super User
Super User

@schrodinger , This need to be something like that

Price Change = IF(Sheet1[Cost] = maxx(FILTER(Sheet1, Sheet1[Item ID]=EARLIER(Sheet1[Item ID])
&& Sheet1[Rank]=EARLIER(Sheet1[Rank])+1 ) ,Sheet1[Cost]),TRUE(),FALSE() )

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.