topic Re: How to get Date of Min Value from table each year in DAX Commands and Tips

How to get Date of Min Value from table each year

adii — Fri, 02 Feb 2024 09:11:10 GMT

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 .

Re: How to get Date of Min Value from table each year

123abc — Fri, 02 Feb 2024 09:44:55 GMT

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:

Open your Power BI Desktop file.
Go to the table where you want to add the calculated column.
Click on "Modeling" in the top menu.
Click on "New Column" in the ribbon.
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.

Re: How to get Date of Min Value from table each year

adii — Fri, 02 Feb 2024 10:22:34 GMT

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

Re: How to get Date of Min Value from table each year

Anonymous — Mon, 05 Feb 2024 02:00:08 GMT

Hi adii

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?

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.

Re: How to get Date of Min Value from table each year

adii — Mon, 05 Feb 2024 16:13:37 GMT

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)

Re: How to get Date of Min Value from table each year

Anonymous — Wed, 28 Feb 2024 08:04:28 GMT

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:

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.

Re: How to get Date of Min Value from table each year

adii — Thu, 29 Feb 2024 19:14:02 GMT

Thanks you 😉 working well