MIN date of a status dependent on a condition.

[NZCraig]

Hi,

I am trying to work out how to create a calucluated column that will provide a returned value of "In schedule" or Out of schedule" based on the week of the year for the start and finish date.

I have a table that has several date columns and a status column, and I can calculate the end date, but I am struggling to calculate the start date. I need to work out the MIN date of the order status change date to INPRG, but only when the order status does not change back to SCHEDULED, or WAPPR, or WSCH ( a quirk of our system that orders that require rescheduling need to be set to In Progress first to re-schedule)

My end goal is two measures, to distinct count orders that were completed in schedule, and out of schedule.

A sample dataset would look like the below, and the desired output is that order 1, and order 3 were completed out of schedule, and order 2 and order 4 were completed in schedule. Because I am working in an audit table, I get multiple rows of the same status when changes are made.

The logic in plain language would be -

Start date = the MIN status date for the last status change to INPRG before completion - this is where I am stuck

End date = the MAX status date for PCOMP ( I have used grouping and a seperate table to calculate this already)

In/Out  = If the start date week is less than the schedstart date week, or the end date week is greater than the schedfinish date week then return "OOS", otheriwse return "IS"

Any help would be greatly appreciated,

Craig

order_num	schedstart	schedfinish	status	status_date	In/Out - (expected result)
1	15/05/2020	20/05/2020	SCHEDULED	20/03/2020	OOS
1	16/05/2020	21/05/2020	SCHEDULED	21/03/2020	OOS
1	16/05/2020	21/05/2020	INPRG	15/05/2020	OOS
1	16/05/2020	21/05/2020	INPRG	16/05/2020	OOS
1	16/05/2020	21/05/2020	INPRG	16/05/2020	OOS
1	16/05/2020	21/05/2020	PCOMP	17/05/2020	OOS
1	16/05/2020	21/05/2020	CLOSE	20/05/2020	OOS
2	20/05/2020	22/05/2020	SCHEDULED	19/04/2020	IS
2	20/05/2020	22/05/2020	INPRG	13/05/2020	IS
2	20/05/2020	22/05/2020	WSCH	13/05/2020	IS
2	20/05/2020	23/05/2020	SCHEDULED	13/05/2020	IS
2	20/05/2020	23/05/2020	INPRG	21/05/2020	IS
2	20/05/2020	23/05/2020	PCOMP	23/05/2020	IS
2	20/05/2020	23/05/2020	COMP	26/05/2020	IS
3	25/05/2020	27/05/2020	SCHEDULED	20/03/2020	OOS
3	25/05/2020	27/05/2020	INPRG	21/03/2020	OOS
3	25/05/2020	26/05/2020	SCHEDULED	20/03/2020	OOS
3	25/05/2020	26/05/2020	INPRG	24/05/2020	OOS
3	25/05/2020	26/05/2020	PCOMP	26/05/2020	OOS
3	25/05/2020	27/05/2020	COMP	28/05/2020	OOS
3	25/05/2020	27/05/2020	CLOSE	28/05/2020	OOS
4	20/05/2020	22/05/2020	SCHEDULED	19/04/2020	IS
4	20/05/2020	22/05/2020	INPRG	13/05/2020	IS
4	20/05/2020	22/05/2020	WSCH	13/05/2020	IS
4	20/05/2020	23/05/2020	SCHEDULED	13/05/2020	IS
4	20/05/2020	23/05/2020	INPRG	21/05/2020	IS
4	20/05/2020	23/05/2020	WAPPR	22/05/2020	IS
4	01/06/2020	03/06/2020	WSCH	22/05/2020	IS
4	31/05/2020	03/06/2020	SCHEDULED	25/05/2020	IS
4	31/05/2020	03/06/2020	INPRG	31/05/2020	IS
4	31/05/2020	03/06/2020	PCOMP	05/06/2020	IS
4	31/05/2020	03/06/2020	COMP	05/06/2020	IS
4	31/05/2020	03/06/2020	CLOSE	10/06/2020	IS

(expectd results  if this helps  )

Order 1 start date = 15/05/2020 end date = 17/05/2020

Order 2 start date = 21/05/2020 end date = 23/05/2020

Order 3 start date = 24/05/2020 end date = 26/05/2020

Order 4 start date = 31/05/2020 end date = 05/06/2020

[ryan_mayu · Accepted solution]

@NZCraig 

Please try to create a column

Column = 
VAR mindate=MAXX(FILTER(Sheet4,Sheet4[order_num]=EARLIER(Sheet4[order_num])&&Sheet4[status]="INPRG"),Sheet4[status_date])
VAR maxdate=MAXX(FILTER(Sheet4,Sheet4[order_num]=EARLIER(Sheet4[order_num])&&Sheet4[status]="PCOMP"),Sheet4[status_date])
return if(WEEKNUM(mindate)<WEEKNUM(Sheet4[schedstart])||WEEKNUM(maxdate)>WEEKNUM(Sheet4[schedfinish]),"OOS","IS")

However the output is different from what you expected.

Order 1

schedulestartdate 2020/5/16, startdate 2020/5/15 and week is not less than schedulestartedate

schedulefinishedate 2020/5/21, enddate 2020/5/17 and week is not greater than schedulefinishdate.

That's why the output is IS not OOS.

[NZCraig]

Thanks Ryan,

When combined with a new table I found within the database, this works for me. Appreciate you help and guidance with this.

Cheers

Craig

[lbendlin]

Your status_date column is ambiguous. For example order 2 had three events on 13/05/2020,  and the date granularity on that colums could impact the logic you want to implement. Is there another column for the event id ? or maybe the Status_date column is actually datetime?

[NZCraig]

 Hi,

I didn't include all columns in the table, there is another column that is date/time that is a transaction timestamp column called eaudittimestamp. I may have thought of another to solve this problem today, but am just working through testing it now.

Thanks

Craig