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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IvanS
Helper V
Helper V

Lookup returning value with earliest & latest date

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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