Forum Discussion
How to create a calculated column with dates based on a set of rules
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!
2 Replies
- amitchandak
Super User
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]
)
)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])
