Lookup returning value with earliest & latest date

[IvanS]

Hi folks,

I am trying to calculate case duration (1 case consists of several tasks) and I amhaving issue with lookup function that should return the value with earlierst and also latest date. Example below:

I have 3 tables:

Table: DIM_DateTable (standard calculated date table)

Table: FACT_Cases with column:

• Case_ID (distinct values in table)

Table: FACT_Tasks with columns:

• Task_ID
• Case_ID (not distinct values in table as several tasks can be related to 1 case)
• Created date
• Close date

I would need to calculate 2 columns in FACT_Case table with dates used in table FACT_Tasks:

• Case created date (Earlierst created date of related tasks)
• Case close date (Latest close date of related tasks)

Any help is much appreciated

IvanS

[amitchandak · Accepted solution]

@IvanS , new columns

Case start = minx(filter(Fact, Case_id = earlier([Case ID]) , [Created Date])

Case Closed= Maxx(filter(Fact, Case_id = earlier([Case ID]) , [Close Date])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[IvanS]

Thank you for prompt help. Worked like a charm! 🙂