How to show percentage increase or decrease of a cost value based on the first or last dated entry

[MLDerrick]

I have a list of the records that I want to show from a SQL database and a line graph that shows those items which I can click on each of the lines and it works fine. But what I want to do next is have another visual that shows the % increase or decrease of the buying price based on the first entry of an item and the last entry. 

The dataset is something like this:

Order No	Supplier	Item	Order date	Quantity	buying price
000123	478	130111	22/07/21	2	1.00
000125	478	130111	22/08/21	5	1.50
000126	478	130111	22/09/21	4	2.00
000127	478	130111	22/10/21	2	1.75
000125	477	130112	22/09/21	10	2.00
000138	477	130112	22/10/21	6	2.05
000150	477	130112	22/11/21	6	1.15

So with item 130111 the buying price of increased based on the first and last price. The buying prince of 130112 did the opposite. But I dont know how PowerBi visualises percentages. 

Cheers...

[MLDerrick]

Hi there, thanks for the reply. 

I can see how the measure works, but for some reason its not giving me the correct percentage value based on some real world data. 

Examples of my data that I need it to calculate. 

Item Code	Date ordered	Unit price
260104	14/04/21	19.50
260104	10/05/21	19.50
260104	15/06/21	24.00
260104	20/07/21	24.00
260104	15/08/21	28.80
260104	04/10/21	28.80
260104	18/11/21	28.80
260104	18/11/21	28.80
260104	18/11/21	49.00
260104	25/11/21	49.00
260104	21/12/21	49.00
260104	28/04/22	49.00
260104	12/07/22	49.00

If I create a new visual for that measure it only gives me a 61.46% increase when its supposed to be more like 151%

Not sure where its getting that figure from. 

Also if I set it up at the 3 seperate measures, the "price per earlyest date" value always comes out at zero, instead of being the correct value for the item. I'm wondering if its picking up the lowest value in the whole table, which as it contains all of the prices for all of the items ever, it would have some items with zero values.

[jcalheir]

Hi

You can create the following metrics:

First one will create the earlyest date by id

min_date_id =
CALCULATE(
    MIN('Table'[date]),
    ALLEXCEPT('Table','Table'[Item])
)

Then create another to find the value for the minimum date:

price per earlyest date =
CALCULATE(
    MIN('Table'[price]),
    FILTER(
        all('Table'),
        'Table'[date] = min_date_id
    )
)

At least create another fot the division

Price % =
DIVIDE(
    sum('Table'[price]),
    [price per earlyest date]
)

or if you want, just put it all in the same metric:

Price % = 

VAR min_date_id =
CALCULATE(
    MIN('Table'[date]),
    ALLEXCEPT('Table','Table'[Item])
)

var min_price_date =
CALCULATE(
    MIN('Table'[price]),
    FILTER(
        all('Table'),
        'Table'[date] = min_date_id
    )
)

RETURN
DIVIDE(
    sum('Table'[price]),
    min_price_date
)

It seems complex, but it is quite simple. Either way, althought this works, if you find a more efficient solution, please share.

Hope it helps

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[MLDerrick]

Also, I found a different measure like this:

% Change =

DIVIDE(

CALCULATE(

SUM('TABLE'[PRICE]),

FILTER('TABLE','TABLE'[Date]=MAX('TABLE'[Date]))),

CALCULATE(

SUM('TABLE'[UnitPrice]),

FILTER('TABLE','TABLE'[Date]=MIN('TABLE'[Date]))),0) - 1

Which gives me a value of 1.51% when its supposed to be 151% 

So it is giving me the right answer, but with the decimal in the wrong place (this is right for all of the other items, 0.47% installed of 47% etc) 

[jcalheir · Accepted solution]

I guess that could work as well. If you only got the decimal place wrong, just multiply the measure result by 100 😁

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂