Datediff between dates in groups

[eacy]

Hi,

I would like to DATEDIFF every time I meet a date in column "CFC True" with a date in column "CC True".

If there is more dates in the "CC True" column since the last date in "CFC True" column then I would like to DATEDIFF with the first date (MIN) since last "CFC True" date.

So the DATEDIFF from the "CFC True" date 20-10-2015 11:10:50 (see picture below) should show "Hours" from that date to 09-10-2015 16:06:20 and not MAX('jiradb jiraaction'[CREATED]) or MIN('jiradb jiraaction'[CREATED]) but MIN("Since last CFC True date")

The following code gives me the correct answer if there is only one "CC True" date since last "CFC True" but if there is several dates I would like to have the first "CC True" date.

IF(
 'jiradb jiraaction'[CFC] = TRUE();
  DATEDIFF(
   CALCULATE(
    MAX('jiradb jiraaction'[CREATED]);
     FILTER('jiradb jiraaction';
      'jiradb jiraaction'[issueid] = EARLIER('jiradb jiraaction'[issueid]) &&
      'jiradb jiraaction'[CREATED] < EARLIER('jiradb jiraaction'[CREATED]) &&
      'jiradb jiraaction'[CC] = TRUE())
   );
  'jiradb jiraaction'[CREATED];HOUR)

)

I have tried with GROUP BY but it seems like I don't understand how to use it, any suggestion is appreciated.

[v-sihou-msft · Accepted solution]

@eacy

Please try again with following two formulas which create two calculated columns. It works with the data you posted here.

Min_CC_Since_Last_CFC = 

VAR LastCFCTrue =

    IF (

        Table1[CFC] = TRUE (),

        MAXX (

            FILTER ( Table1, Table1[CFC True] < EARLIER ( Table1[CFC True] ) ),

            Table1[CFC True]

        ),

        BLANK ()

    )

RETURN

    (

        IF (

            Table1[CFC] = TRUE (),

            MINX (

                FILTER (

                    Table1,

                    Table1[CC] = TRUE ()

                        && Table1[CC True] < EARLIER ( Table1[CFC True] )

                        && Table1[CC True] > LastCFCTrue

                ),

                Table1[CC True]

            ),

            BLANK ()

        )

)

CFC_CC_Hour_Diff =

DATEDIFF ( Table1[Min_CC_Since_Last_CFC], Table1[CFC True], HOUR )

[v-sihou-msft]

@eacy

In this scenario, you can create a calculated column to get the first date (MIN) in “CC True” from the last date in “CFC True” with following formula:

CC_Since_Last_CFC = 
IF (
    Table1[CFC] = TRUE (),
    MINX (
        FILTER (
            Table1,
            Table1[CC] = TRUE ()
                && Table1[CC True] > EARLIER ( Table1[CFC True] )
        ),
        Table1[CC True]
    ),
    BLANK ()
)

Then you can create another calculated column to get the DATEDIFF result with formula below:

CFC_CC_Day_Diff = 
DATEDIFF ( Table1[CFC True], Table1[CC_Since_Last_CFC], DAY )

  

[eacy]

Hi Simon,

If I delete the line

&& Table1[CC True] > EARLIER ( Table1[CFC True] )

then I can save the calculated column

but with the line I cannot save it, it just keep on "Working on it..." (app. 875.000 rows)

And I actually want to get the previous "CC True" date and not the next. but I belive it is a matter of changing ">" to "<" right?

Do you have any ideas why it just keep on "Working on it..."

[v-sihou-msft · Accepted solution]

@eacy

Please try again with following two formulas which create two calculated columns. It works with the data you posted here.

Min_CC_Since_Last_CFC = 

VAR LastCFCTrue =

    IF (

        Table1[CFC] = TRUE (),

        MAXX (

            FILTER ( Table1, Table1[CFC True] < EARLIER ( Table1[CFC True] ) ),

            Table1[CFC True]

        ),

        BLANK ()

    )

RETURN

    (

        IF (

            Table1[CFC] = TRUE (),

            MINX (

                FILTER (

                    Table1,

                    Table1[CC] = TRUE ()

                        && Table1[CC True] < EARLIER ( Table1[CFC True] )

                        && Table1[CC True] > LastCFCTrue

                ),

                Table1[CC True]

            ),

            BLANK ()

        )

)

CFC_CC_Hour_Diff =

DATEDIFF ( Table1[Min_CC_Since_Last_CFC], Table1[CFC True], HOUR )

[eacy · Accepted solution]

Hi Simon,

I got my result, thanks a lot.