Forum Discussion
Solved
Datediff between dates in groups
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" colum...
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 )
Hi Simon,
I got my result, thanks a lot.
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..."
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 )
Hi Simon,
I got my result, thanks a lot.