cancel
Showing results 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.

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 .

1 ACCEPTED SOLUTION
Community Support

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:

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.

6 REPLIES 6
Community Support

I ran the testing as follows.

My sample:

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]))``````

Is this the result you expect?

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.

Helper I

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)

Community Support

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:

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.

Helper I

Thanks you 😉 working well

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.

Helper I

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors