topic Re: Urgent! Days interval same id different status in DAX Commands and Tips

Urgent! Days interval same id different status

johnmay — Thu, 21 May 2020 00:08:48 GMT

Hello people!

Please, I need help to create a measure that is able to analyze the range of days from an "accept" status to the next in the same ID. However, there are 3 different scenarios:

10670 - If I have the status "proposal" I calculate the range of days from the status "accept" to it (in this case the interval is 10 days)

10671 - If I don't have the "proposal" I calculate from "accept" until the next status (in this case the next status is "done" and the interval will be 9 days)

10672 - If I don't have any status after "accept" I calculate the interval until today (14 days interval )

I need this measure for a tachometer view that will report the average of the days that in this case will be 10 + 9 + 14 / 3 IDs = 11 days

Work Item Id	STATE DATE	State
10670	23/04/2020	In Progress
10670	06/05/2020	Accept
10670	16/05/2020	Proposal
10671	23/04/2020	In Progress
10671	06/05/2020	Accept
10671	15/05/2020	Done
10672	23/04/2020	In Progress
10672	06/05/2020	Accept

Re: Urgent! Days interval same id different status

Greg_Deckler — Thu, 21 May 2020 02:05:14 GMT

Same answer as the other one of these:

Measure = VAR __Table = 'Table' VAR __States = SELECTCOLUMNS(__Table,"State",[State]) VAR __MaxDate = MAX('Table'[STATE DATE]) VAR __MaxStatus = MAXX(FILTER('Table',[STATE DATE] = __MaxDate),[State]) RETURN SWITCH(TRUE(), "Proposal" IN __States, VAR __date1 = MAXX(FILTER(__Table,[State] = "Proposal"),[STATE DATE]) VAR __date2 = MAXX(FILTER(__Table,[State] = "Accept"),[STATE DATE]) RETURN (__date1 - __date2) * 1., __MaxStatus = "Accept",(TODAY() - __MaxDate) * 1., VAR __date1 = MAXX(FILTER(__Table,[State] = "Accept"),[STATE DATE]) VAR __date2 = MINX(FILTER(__Table,[State] <> "Accept" && [STATE DATE] > __date1),[STATE DATE]) RETURN (__date2 - __date1) * 1. )

Re: Urgent! Days interval same id different status

johnmay — Thu, 21 May 2020 20:03:18 GMT

Thank you so much! It works perfectly.

Re: Urgent! Days interval same id different status

Greg_Deckler — Thu, 21 May 2020 21:09:24 GMT

Sweet! Please try not to cross post next time! 🙂