topic Re: % price change since last sale in DAX Commands and Tips

% price change since last sale

pawelj795 — Thu, 24 Oct 2019 10:57:27 GMT

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! ;)

Re: % price change since last sale

Anonymous — Thu, 24 Oct 2019 12:30:08 GMT

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)

Re: % price change since last sale

Anonymous — Thu, 24 Oct 2019 12:31:47 GMT

You can refer this link
https://community.powerbi.com/t5/Desktop/How-to-calculate-percentage-change-between-two-dates/td-p/494008

Re: % price change since last sale

pawelj795 — Thu, 24 Oct 2019 12:44:32 GMT

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.

Re: % price change since last sale

Anonymous — Thu, 24 Oct 2019 12:48:21 GMT

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.

Re: % price change since last sale

pawelj795 — Thu, 24 Oct 2019 13:05:42 GMT

Something is wrong :(

To be clear, under indexes there are invoice numbers.

Re: % price change since last sale

pawelj795 — Thu, 24 Oct 2019 14:23:16 GMT

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

What I am doing wrong?