Group By with min date but keep other columns

[Myst]

So below is the dataset I'm working with. 

WR	Status	Date	WO
1	CREA	20200103
1	APPR	20200225	101
2	CREA	20200223
2	APPR	20200225	101
3	CREA	20200420
4	CREA	20200423
4	APPR	20200424	103
5	CREA	20200606
6	ORIG	20200607

Need to sort by WO and find the date difference between the 2 different status and WR it refers to. If one WO refers to two different WRs, find the difference between the older WR and WO. Below is how the sorted table would look like this. Note how WO 101 has two different WRs and days were calculated using WR 1. 

WR	WO	Days
1	101	53
4	103	1

Would this be possible through DAX or Power Query Editor? 

[v-angzheng-msft · Accepted solution]

Hi, @Myst 

Try this:

_WO column = 
MAXX(FILTER(ALL('Table'),[WR]=EARLIER('Table'[WR])),[WO])

_OlderDate_APPPR = 
IF(MAX('Table'[WO])<>BLANK()&&MAX('Table'[WR])=MINX(FILTER(ALL('Table'),'Table'[WO]=MAX('Table'[WO])),[WR]),
MINX(FILTER(ALL('Table'),'Table'[WO]=MAX('Table'[WO])&&'Table'[Status]="APPR"),[Date]))

_CREA = 
var _WO=MAXX(FILTER(ALL('Table'),'Table'[WR]=MAX('Table'[WR])),[WO])
var _WR=SUMMARIZE(FILTER(ALL('Table'),'Table'[WO]=_WO),[WR])
RETURN
IF(MAX('Table'[WO])<>BLANK()&&MAX('Table'[WR])=MINX(FILTER(ALL('Table'),'Table'[WO]=MAX('Table'[WO])),[WR]),
MINX(FILTER(ALL('Table'),'Table'[WR] in _WR&&'Table'[Status]="CREA"),[Date]))

_Days = DATEDIFF([_CREA],[_OlderDate_APPPR],DAY)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Myst]

Would it be possible to get the WO number in all columns based on WR? So something like this:

WR	Status	Date	WO
1	CREA	20200103	101
1	APPR	20200225	101
2	CREA	20200223	101
2	APPR	20200225	101
3	CREA	20200420
4	CREA	20200423	103
4	APPR	20200424	103
5	CREA	20200606
6	CREA	20200607

[Anonymous]

is this what you are looking for?

[MattAllington]

This video I created should help you https://youtu.be/xN2IRXQ2CvI

[Myst]

So it's somewhat similar but I don't know how to approch the open and close dates being on the same column. I need to remove the new CREA date and row if its APPR WO is a duplicate of an old WR.