Urgent! Measure Days diff same ID any status

[johnmay]

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

[v-deddai1-msft · Accepted solution]

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/EdhMBMaKqgpPlfRD9I...

Best Regards,

Dedmon Dai

[johnmay]

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

[Greg_Deckler]

Dude, please don't cross post, this is just ridiculous now.

[Greg_Deckler · Accepted solution]

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.

[v-deddai1-msft · Accepted solution]

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/EdhMBMaKqgpPlfRD9I...

Best Regards,

Dedmon Dai

[Greg_Deckler]

Seriously, another one?