Measure related to a certain date

[ma_ltei]

Hello dear Power Bi community users,

i'm trying to do somehting which i'm sure is easy to do but that i'm getting mad about 😄

I have a table where each line contains some informations for a certain date :

Date	Product	Price	Index	Factor
2022-01-01	A	2,5 €	1	2,5
2022-01-01	B	2,9 €	1	2,9
2022-02-01	A	3,4 €	1,1	3,09
2022-02-01	B	3,2 €	1,1	2,91
2022-03-01	A	4,1 €	1,2	3,41
2022-03-01	B	3,8 €	1,2	3,16

My goal here is to display two lines :

First one : Price evolution per date

Seconde one : Index evolution rebased on 2022-01-01 ( Index values * 2022-01-01 factor )

So for product A : 1 / 1,1 / 1,2 * 2,5 = 2,5 / 2,75 / 3

So for product B : 1 / 1,1 / 1,2 * 2,9 = 2,9 / 3,19 / 3,48

To do this I created a measure that multiplies the Index value per the factor value that is calculated for the MIN of date so that it adapts automatically to the product selected, but it doesnt work :

it does show this : Product A : 1 * 2,5 / 1,1 * 3,09 / 1,2 * 3,41 = 2,5 / 3,4 / 4,1

instead of this : Product A :      1 / 1,1 / 1,2 * 2,5                     = 2,5 / 2,75 / 3

Here is the measure used : factor_to_use = CALCULATE(MIN(Sheet1[Factor]), FILTER(Sheet1,Sheet1[Date]=MIN(Sheet1[Date])),ALLEXCEPT(Sheet1,Sheet1[Product]))

Can you help me correct the measure OR find the correct formula to display in the factor column only the factor value calculated for the min date available per product ?

Thanks a lot 🙂

[v-jianboli-msft · Accepted solution]

Hi @ma_ltei ,

Approve with @amitchandak , the MIN(Sheet1[Factor]) is not simply calculate the min of the value, it is used with FIRSTNONBLANK(), it returns the first value in the column, column, filtered by the current context, where the expression is not blank. So you will get the value of the factor for the min date.

Besides, here is another solution:

Measure 2 =

var _a = CALCULATE(MIN([Date]),ALLEXCEPT(Sheet1,Sheet1[Product]))

return CALCULATE(MIN('Sheet1'[Factor]),FILTER(ALL('Sheet1'),[Date]=_a&&[Product]=MAX([Product])))

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@ma_ltei , try like

Calculate(firstnonblankvalue(Sheet1[Date], MIN(Sheet1[Factor])), ALLEXCEPT(Sheet1,Sheet1[Product]))

Use in place of max here

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

[ma_ltei]

Hello amitchandak,

Thanks a lot but i don't understand the MIN(Sheet1[Factor]) as I dont need the min of the value but the value of the factor for the min date where this information is available ?