Forum Discussion
Urgent! Measure Days diff same ID any 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:
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 |
Maybe:
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. )PBIX is attached.
Hi,
Would you please try to create a measure for calculating datediff for every situation:
Measure =
VAR A =
CALCULATE (
VALUES ( 'Table'[STATE DATE] ),
FILTER (
'Table',
'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] )
&& 'Table'[State] = "proposal"
)
)
VAR B =
CALCULATE (
VALUES ( 'Table'[STATE DATE] ),
FILTER (
'Table',
'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] )
&& 'Table'[State] = "Accept"
)
)
VAR C =
CALCULATE (
MAX ( 'Table'[STATE DATE] ),
FILTER ( 'Table', 'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] ) )
)
RETURN
SWITCH (
TRUE (),
NOT ( A = BLANK () ), DATEDIFF ( B, A, DAY ),
B = C, DATEDIFF ( B, TODAY (), DAY ),
DATEDIFF ( B, C, DAY )
)
Then create a measure to calculate their average:
Measure 2 = AVERAGEX(SUMMARIZE('Table','Table'[Work Item Id],"_datediff",[Measure]),[_datediff])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdhMBMaKqgpPlfRD9IwGZu8B72HelpwkWMI3OEf6BzKhmQ?e=ErDYgp
Best Regards,
Dedmon Dai
5 Replies
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
Maybe:
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. )PBIX is attached.
Dude, please don't cross post, this is just ridiculous now.
- Seriously, another one?
Hi,
Would you please try to create a measure for calculating datediff for every situation:
Measure =
VAR A =
CALCULATE (
VALUES ( 'Table'[STATE DATE] ),
FILTER (
'Table',
'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] )
&& 'Table'[State] = "proposal"
)
)
VAR B =
CALCULATE (
VALUES ( 'Table'[STATE DATE] ),
FILTER (
'Table',
'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] )
&& 'Table'[State] = "Accept"
)
)
VAR C =
CALCULATE (
MAX ( 'Table'[STATE DATE] ),
FILTER ( 'Table', 'Table'[Work Item Id] = MAX ( 'Table'[Work Item Id] ) )
)
RETURN
SWITCH (
TRUE (),
NOT ( A = BLANK () ), DATEDIFF ( B, A, DAY ),
B = C, DATEDIFF ( B, TODAY (), DAY ),
DATEDIFF ( B, C, DAY )
)
Then create a measure to calculate their average:
Measure 2 = AVERAGEX(SUMMARIZE('Table','Table'[Work Item Id],"_datediff",[Measure]),[_datediff])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdhMBMaKqgpPlfRD9IwGZu8B72HelpwkWMI3OEf6BzKhmQ?e=ErDYgp
Best Regards,
Dedmon Dai
