Hi, working on a calculated column to return "During", "Outside", "7 days pre" or "7 days post" to see if an [ID] from table 'Incidents' has a start time that falls inside or outside [7 days pre] or [7 days post].
During is defined as falling in between [Start Time] and [End Time]. [Start Time], [End Time], [7 days pre] and [7 days post] all exist in a related table 'Freezes'
Here's the code, it however won't return Outside whatever I do to play around with it. Have tried split into 2 columns to recalculate if outside in a separate column.
Help! (I will not share a sample data model as that is not required here)
When5 =
VAR IncidentFreezes = ALL('Freezes')
VAR IncidentFreeze = CALCULATE(
MAX('Freezes'[Start Time]),
FILTER(
IncidentFreezes,
'Freezes'[7 days pre] <= 'Incidents'[I.Start Time] && 'Freezes'[7 days post] >= 'Incidents'[I.Start Time]
)
)
RETURN
IF(
'Incidents'[I.Start Time] >= MIN('Freezes'[7 days post]) && 'Incidents'[I.Start Time] >= MAX('Freezes'[End Time]),
"Outside",
IF(
'Incidents'[I.Start Time] >= MIN('Freezes'[Start Time]) && 'Incidents'[I.Start Time] < MAX('Freezes'[End Time]),
"During",
IF(
'Incidents'[I.Start Time] >= MIN('Freezes'[7 days pre]) && 'Incidents'[I.Start Time] < MIN('Freezes'[Start Time]),
"7 days pre",
IF(
'Incidents'[I.Start Time] <= MAX('Freezes'[7 days post]) && 'Incidents'[I.Start Time] >= IncidentFreeze,
"7 days post",
IF(
AND(
ISBLANK(IncidentFreeze),
'Incidents'[I.Start Time] >= MAX('Freezes'[End Time])
),
"7 days post",
BLANK()
)
)
)
)
)
