Forum Discussion
Solved
Urgent! Days interval same id different status
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: 1067...
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. )
Greg_Deckler
Community Champion
Community ChampionSame 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.
)Thank you so much! It works perfectly.
- Greg_DecklerSweet! Please try not to cross post next time! 🙂
