Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
adii
Helper I
Helper I

How to get Date of Min Value from table each year

Hello. I have problem with write measure to take a date with min value from column.

 

I have table with  year column and measure in column (pct header ) with min of pct at this year. 

So now I need recive next column with date of minimum values when it's occurred for example 2017-04-27 for 2017, 2019/04-21 for 2019 etc . problem is , the date of occurred could be not unique 

 

pleas help . I've run of ideas .

IMG_2493.jpeg

 

 

 

 

1 ACCEPTED SOLUTION

Hi @adii 

 

I would like to apologize for the belated reply.

 

You can create measures as follow.

 

minpct = CALCULATE(MIN([pct]), ALLEXCEPT('Table', 'Table'[Year]))

 

 

 

minDate = CALCULATE(MAX([Date]), FILTER('Table', [pct] = [minpct]))

 

 

Output:

vxuxinyimsft_0-1709107378784.png

 

Best Regards,
Yulia Xu

 

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

6 REPLIES 6
v-xuxinyi-msft
Community Support
Community Support

Hi @adii 

 

I ran the testing as follows.

 

My sample:

vxuxinyimsft_0-1707097971780.png

 

Create a measure as follows

 

Measure = 
VAR _minpct = CALCULATE(MIN('Table'[pct]), ALLEXCEPT('Table', 'Table'[Year]))
RETURN
CALCULATE(MIN('Table'[Date]), ALLEXCEPT('Table', 'Table'[Year]))

 

 

vxuxinyimsft_1-1707098096574.png

Is this the result you expect?

 

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account. We can better understand the problem and help you.

 

Best Regards,
Community Support Team _Yuliax

 

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

 Thanks for your time . Yours proposing solving doesn't work. I couldn't make pbx file so I added photo of two tables with data, left is raw date and I need recive date like table at right side of photo.

Measure with min value is correct but I need createmeasure wich result will be column with date (right table at photo) 

IMG_2500.jpeg

Hi @adii 

 

I would like to apologize for the belated reply.

 

You can create measures as follow.

 

minpct = CALCULATE(MIN([pct]), ALLEXCEPT('Table', 'Table'[Year]))

 

 

 

minDate = CALCULATE(MAX([Date]), FILTER('Table', [pct] = [minpct]))

 

 

Output:

vxuxinyimsft_0-1709107378784.png

 

Best Regards,
Yulia Xu

 

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

Thanks you 😉 working well 

123abc
Community Champion
Community Champion

To achieve this in Power BI, you can create a calculated column using DAX (Data Analysis Expressions). Here's a step-by-step guide to creating the calculated column:

  1. Open your Power BI Desktop file.
  2. Go to the table where you want to add the calculated column.
  3. Click on "Modeling" in the top menu.
  4. Click on "New Column" in the ribbon.
  5. Enter the following DAX formula for the calculated column:

 

Date of Min Value =
VAR MinPct = MIN(Table1[pct])
RETURN
CALCULATE(
MIN(Table1[Date]),
FILTER(
Table1,
Table1[pct] = MinPct && Table1[year] = EARLIER(Table1[year])
)
)

 

Replace Table1 with the name of your table, [pct] with the name of your percentage column, [Date] with the name of your date column, and [year] with the name of your year column.

This formula uses the MIN() function to find the minimum percentage value for each year. Then, it calculates the minimum date corresponding to that minimum percentage value for each year using the CALCULATE() and FILTER() functions. The EARLIER() function is used to refer to the current row context within the filter context.

Once you've entered the formula, press Enter to create the calculated column.

Now, you should have a new column called "Date of Min Value" in your table, which will display the date of the minimum value for each year in your data set.

Please adjust the column names and table names in the formula to match your actual data model.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

I tried do like you, but doesn't work correctly 

image.jpg

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors