How to create a calculated column with dates based on a set of rules

[ac22]

Hello,

I am new to Power BI and I am having a hard time trying to generate these calculated dates in DAX. I have two tables:

1. Requests = A list of requests 

2. Detail = Detailed level with individual items on the request

Individual detailed items can be put on hold at different times, but the request should not be put on Hold unless ALL of the ACTIVE individual detail rows are on Hold at the same time. I am trying to calculate the Hold Start and End dates on the request.

Consider the following data:
Detail

ID	Request ID	Status	Received Date	Hold Start	Hold End
R1-01	R1	Not Started	7/1/2023
R1-02	R1	Hold	7/1/2023	8/1/2023
R2-01	R2	Hold	7/1/2023	8/1/2023
R2-02	R2	Hold	7/1/2023	9/1/2023
R2-03	R2	Not Started	9/5/2023
R3-01	R3	On Track	8/1/2023	8/1/2003	8/15/2023
R3-02	R3	On Track	8/1/2023	8/20/2023	8/31/2023
R4-01	R4	On Track	7/15/2023	8/1/2023	8/20/2023
R4-02	R4	On Track	7/15/2023	8/10/2023	8/30/2023
R4-03	R4	On Track	9/1/2023

Request

ID	Status	Hold Start	Hold End
R1	Hold	8/1/2023
R2	Hold	9/1/2023
R3	Started
R4	Started	8/10/2023	8/20/2023

• In R1, there are two detail rows, but only one is active and is on Hold, so the Hold Start and End dates match the R1-02 row.
• In R2, there are two "active" detail rows that are on hold. Both were received on 7/1/23. R2-01 started Hold on 8/1, but R2-02 was active until 9/1, so that's the overall Request Hold Start Date.
• In R3, there are two active rows that were on hold on separate periods of time, but since there is no overlap in the dates, the overall Request was never on Hold.
• In R4, there are 3 active detail rows. Two of them were added on 7/15 and they were on hold on separate periods of time. In this case, there was overlap in the hold period, so the overall request hold start and end dates is the overlap period.
  • There is a third detail row that was added on 9/1/23. Since it was added after the overlapping period, then the Request Hold Start and End dates still stand.
  • If the third row had a Received Date of 8/15 (for instance), then the overall request Hold End Date should be 8/15 as well (the date when at least one of the detail rows was active and not on hold).

So far, I have no problem filtering out the "Not Started" detail rows, but I'm not sure how to iterate through the different detail rows to compare the different dates and determine the correct one. Especially since I don't believe such iteration is possible in DAX?

RequestCalcHoldStartDate = 
VAR _req = [RequestID]
VAR _list =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER('Detail', 'Detail'[RequestID] = _req && 'Detail'[Status] <> "Not Started"),
            'Detail'[ReceivedDate], 'Detail'[HoldStartDate]),
        "HoldStartDt",
        [HoldStartDt]
    )

RETURN
    MAXX(FILTER(_list, [HoldStartDt]), [HoldStartDt])

I also tried creating a table first to see if I had a better way of manipulating it in the calculated column formula, but didn't have much luck.

RequestHoldCalcTable = 
SUMMARIZE(
    FILTER('Detail', 'Detail'[Status] <> "Not Started"),
    'Detail'[RequestID], 'Detail'[ReceivedDate], 'Detail'[EndDate],
    "HoldStartDate", MAX('Detail'[HoldStartDate]),
    "HoldEndDate", MIN('Detail'[HoldEndDate])
)

Thank you so much for your time and help!

[amitchandak]

@ac22 , Based on what I got so far

Create calculated columns in your Request table

RequestHoldStartDate =
CALCULATE(
MAX('Detail'[Hold Start]),
FILTER(
ALL('Detail'),
'Detail'[Request ID] = EARLIER('Request'[ID]) &&
'Detail'[Status] = "Hold"
),
FILTER(
ALL('Request'),
'Request'[ID] = EARLIER('Request'[ID])
)
)

RequestHoldEndDate =
VAR _reqID = 'Request'[ID]
VAR _holdStart = [RequestHoldStartDate]
VAR _activeDetailRows =
FILTER(
'Detail',
'Detail'[Request ID] = _reqID &&
'Detail'[Received Date] <= _holdStart &&
'Detail'[Status] <> "Not Started"
)
VAR _minHoldEnd =
CALCULATE(
MIN('Detail'[Hold End]),
_activeDetailRows,
'Detail'[Hold Start] <= _holdStart
)
RETURN
IF(
ISBLANK(_minHoldEnd),
BLANK(),
MINX(
FILTER(
_activeDetailRows,
'Detail'[Hold End] > _holdStart
),
'Detail'[Hold End]
)
)

[ac22]

Thank you so much, @amitchandak !

I was not familiar with the EARLIER function, so I'm reading and trying out your solution.

For the Hold Start Date, I think I need to change the filter on the status, because technically the detail row may no longer be on status Hold, but have a hold start and end date that we want to capture at the request level. I am adding more data examples below. Here is my updated code so far:

RequestCalcHoldStartDate = 
CALCULATE(
    MAX('Detail'[HoldStart]),
    FILTER(
        ALL('Detail'),
        'Detail'[RequestID] = EARLIER('Requests'[ID]) &&
        'Detail'[Status] <> "Not Started"
    ),
    FILTER(
        ALL('Requests'),
        'Requests'[ID] = EARLIER('Requests'[ID])
    )
)

However, this still does not solve some of the rules. Here are additional data examples:

Requests
ID	Status	Hold Start	Hold End
R5	Completed	9/15/2023	10/20/2023
R6	Started
R7	Started	8/15/2023	8/20/2023

Detail
ID	Request ID	Status	Received Date	Hold Start	Hold End	EndDate
R5-01	R5	Completed	9/1/2023	9/15/2023	10/20/2023	11/1/2023
R6-01	R6	On Track	7/1/2023	8/5/2023	8/20/2023
R6-02	R6	On Track	8/1/2023
R7-01	R7	On Track	7/1/2023	8/5/2023	8/20/2023
R7-02	R7	Completed	8/1/2023			8/15/2023

Originally I didn't include the overall Detail.EndDate since I was trying to simplify the data. But I am realizing that it is probably also needed in the calculation. In these examples:

• R5 has only one detail row that is now completed, but it was at some point in time on Hold. We want to indicate this at the overall request level, so the Request Hold start and end dates match the detail Hold Start and End Dates
• R6 has two detail rows received at different times. The one received first was put on hold after we received the second one, so the overall request was never on hold. Hold dates are blank on the request.
• R7 also has two detail rows received at different times. The one received first was also put on hold after we received the second one. But in this case, the second one got completed before the first one ended its hold. So in this case, the request was put on hold from the End Date of the second detail row, until the Hold End Date of the remaining one (8/15/23 - 8/20/23).

I was trying to add the Detail.ReceivedDate to the RequestCalcHoldStartDate calculation, but I don't know how to compare it to the other detail rows that match the RequestID.

This didn't seem to work:

'Detail'[Received Date] <= EARLIER('Detail'[Received Date])