Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ac22
Regular Visitor

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

IDRequest IDStatusReceived DateHold StartHold End
R1-01R1Not Started7/1/2023  
R1-02R1Hold7/1/20238/1/2023 
R2-01R2Hold7/1/20238/1/2023 
R2-02R2Hold7/1/20239/1/2023 
R2-03R2Not Started9/5/2023  
R3-01R3On Track8/1/20238/1/20038/15/2023
R3-02R3On Track8/1/20238/20/20238/31/2023
R4-01R4On Track7/15/20238/1/20238/20/2023
R4-02R4On Track7/15/20238/10/20238/30/2023
R4-03R4On Track9/1/2023  

 

Request

IDStatusHold StartHold End
R1Hold8/1/2023 
R2Hold9/1/2023 
R3Started  
R4Started8/10/20238/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 2
amitchandak
Super User
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
IDStatusHold StartHold End
R5Completed9/15/202310/20/2023
R6Started  
R7Started8/15/20238/20/2023

 

Detail 
IDRequest IDStatusReceived DateHold StartHold EndEndDate
R5-01R5Completed9/1/20239/15/202310/20/202311/1/2023
R6-01R6On Track7/1/20238/5/20238/20/2023 
R6-02R6On Track8/1/2023   
R7-01R7On Track7/1/20238/5/20238/20/2023 
R7-02R7Completed8/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])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.