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

[Anonymous]

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. 

. 

[Anonymous]

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. 

[Anonymous]

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. 

[Anonymous]

Hi, this helps but I need to use Group By

[Dangar332]

hi, @Anonymous 

what's with blank value 

means how  to compare date with blank value 

[Anonymous]

@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.

[Dangar332]

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)

[Anonymous]

Hi,

This helps but I need to use Group by