topic Re: DAX Formula to find latest date value in a group-GROUP BY in DAX Commands and Tips

DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Mon, 22 Jan 2024 17:34:18 GMT

Hello All,

I have four dates. I need to compare ship date to the latter of the three using GROUP BY. A product ships(Ship date) and it goes into one of three dates depending on it's workflow. These are the three dates that need to be grouped: Submission Date, Price Approved Date and Terms Approved date.

A product either goes to automatic approval(submission date) or a special price or term was requested for the product(Price Approved or Terms Approved date). Once it goes to one of those three dates it then needs to be pushed to invoicing.

Trying to figure out the days between the ship date and whichever of the three dates is the "last" one. I need to group by the three dates and compare them to the ship date.

Re: DAX Formula to find latest date value in a group-GROUP BY

Dangar332 — Fri, 12 Jan 2024 18:28:58 GMT

hi, Anonymous

what's with blank value

means how to compare date with blank value

Re: DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Fri, 12 Jan 2024 18:42:55 GMT

Dangar332

There's multiple physical dates(Basically Order Start Date) where they each have a unique identifier, but each unique identifier with its own physical date has multiple dates(the three other date columns I have) that we would say it's completed.

And it's the Max of those three dates that tells us when the order was completed.

Basically I need to know the number of days from start(physical date) to Finish.

Re: DAX Formula to find latest date value in a group-GROUP BY

Dangar332 — Fri, 12 Jan 2024 19:24:53 GMT

hi, Anonymous

as understood your problem

measure = var a = calculate(min(tablename[physicaldate]),allexcept(tablename,tablename[uniqueidentifiercolumn])) var b = calculate(max(tablename[physicaldate]),allexcept(tablename,tablename[uniqueidentifiercolumn])) return datediff(a,b,day)

Re: DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Fri, 19 Jan 2024 07:33:37 GMT

Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create calculated columns.

max date = var _a=IF([Price Approved Date]>=[Terms Approved Date], [Price Approved Date],[Terms Approved Date]) return IF(_a>= [Submission Date],_a,[Submission Date])

the number of days between = DATEDIFF([Physical Date],[max date],DAY)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

Re: DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Mon, 22 Jan 2024 17:35:20 GMT

Hi, this helps but I need to use Group By

Re: DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Mon, 22 Jan 2024 17:35:55 GMT

Hi,

This helps but I need to use Group by

Re: DAX Formula to find latest date value in a group-GROUP BY

Anonymous — Tue, 23 Jan 2024 07:43:30 GMT

Hi Anonymous ,

We can create calculated columns.

max date = var _a=IF([Price Approved Date]>=[Terms Approved Date], [Price Approved Date],[Terms Approved Date]) return IF(_a>= [Submission Date],_a,[Submission Date])

the number of days between = DATEDIFF([Physical Date],[max date],DAY)

Then we can create a table.

Table 3 = GROUPBY('Table',[Price Approved Date],[Submission Date],[Terms Approved Date],[max date],[the number of days between])

If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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