Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ma_ltei
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 :

 

DateProductPriceIndexFactor
2022-01-01A2,5 €12,5
2022-01-01B2,9 €12,9
2022-02-01A3,4 €1,13,09
2022-02-01B3,2 €1,12,91
2022-03-01A4,1 €1,23,41
2022-03-01B3,8 €1,23,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
v-jianboli-msft
Community Support
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:

vjianbolimsft_0-1659518684838.png

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.

View solution in original post

3 REPLIES 3
v-jianboli-msft
Community Support
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:

vjianbolimsft_0-1659518684838.png

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
Super User
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

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 ?

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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