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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pawelj795
Post Prodigy
Post Prodigy

% price change since last sale

Hello!

I'm currently calculating % price change since last sale (sorted by months).
For example, I sold Product A for 100$ on 5.01.2019 and the next sale of the exactly same product were on 10.03.2019 for 80$.

Thus, I need table/chart, which will be showing that price change -> in this case is -20% on March 2019.

 

To get things more complicated, I have 3 main product groups with a few unique index in every group.

 

I share below some of pbix sample.
https://drive.google.com/open?id=1mKvql1qUVHh69lhoeQSdHakKKJ40h7_9

 

Thanks in advance for any help! 😉

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Change.png

@pawelj795 
Can you please try this

Measure =
VAR PrevDate = CALCULATE(MAX(Test[Date]),FILTER(ALL(Test[Date]),Test[Date]<MAX(Test[Date])))
VAR PrevPrice = CALCULATE(SUM(Test[Price]),Test[Date]=PrevDate)
Return DIVIDE(SUM(Test[Price])-PrevPrice,PrevPrice,0)

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I think I should use AVERAGE instead of SUM because I could have a few sales for different prices in same month.
What do you think?

Another important thing, how to hide values in first month?
I want to show prices only for current year.

Anonymous
Not applicable

This code will compare the sales of current date with last date with sale. So you don't need to worry about that.  Even if you have sales on multiple dates of a month or everyday this code will work fine. 

Something is wrong 😞mistake.png

To be clear, under indexes there are invoice numbers.

I tried slightly modified solution which you mention but still it doesn't working.

What I am doing wrong?mistake_v2.png

Anonymous
Not applicable

Change.png

@pawelj795 
Can you please try this

Measure =
VAR PrevDate = CALCULATE(MAX(Test[Date]),FILTER(ALL(Test[Date]),Test[Date]<MAX(Test[Date])))
VAR PrevPrice = CALCULATE(SUM(Test[Price]),Test[Date]=PrevDate)
Return DIVIDE(SUM(Test[Price])-PrevPrice,PrevPrice,0)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors