cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

## Measure related to a certain date

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 🙂

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User

@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

Regular Visitor

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 ?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors