cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Data modeling with multiple date columns

Hey everyone, 

 

I have the following datamodel and would like to know how to calculate the following, you can also see on the document attached where I need the columns, or if it would be easier with measures

 

 

 

DataModel.png

 

Example1.png

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @chdelgado ,

 

Based on your description, you can as do some steps follows.

  • Using calculated columns:
Real StartDate_column = MINX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = EARLIER('Task Manager'[ID Project])&&'Task Manager'[Task]<=EARLIER('Task Manager'[Task])), 'Task Manager'[Start Date])

Real EndDate_column = MAXX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = EARLIER('Task Manager'[ID Project])), 'Task Manager'[End Date])

Diff vs StartDates_measure = VALUE([Real StartDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule Start Date]))

Diff vs EndDates_measure = VALUE([Real EndDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule End Date]))

 

  • Using measures:
Real StartDate_measure = MINX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = SELECTEDVALUE('Task Manager'[ID Project])&&'Task Manager'[Task]<=MAX('Task Manager'[Task])), 'Task Manager'[Start Date])

Real EndDate_measure = MAXX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = SELECTEDVALUE('Task Manager'[ID Project])), 'Task Manager'[End Date])

Diff vs StartDates_measure = VALUE([Real StartDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule Start Date]))

Diff vs EndDates_measure = VALUE([Real EndDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule End Date]))

 

Result:

v-yuaj-msft_0-1606701524447.png

 

v-yuaj-msft_1-1606701524452.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @chdelgado ,

 

Based on your description, you can as do some steps follows.

  • Using calculated columns:
Real StartDate_column = MINX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = EARLIER('Task Manager'[ID Project])&&'Task Manager'[Task]<=EARLIER('Task Manager'[Task])), 'Task Manager'[Start Date])

Real EndDate_column = MAXX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = EARLIER('Task Manager'[ID Project])), 'Task Manager'[End Date])

Diff vs StartDates_measure = VALUE([Real StartDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule Start Date]))

Diff vs EndDates_measure = VALUE([Real EndDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule End Date]))

 

  • Using measures:
Real StartDate_measure = MINX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = SELECTEDVALUE('Task Manager'[ID Project])&&'Task Manager'[Task]<=MAX('Task Manager'[Task])), 'Task Manager'[Start Date])

Real EndDate_measure = MAXX(FILTER(ALL('Task Manager'), 'Task Manager'[ID Project] = SELECTEDVALUE('Task Manager'[ID Project])), 'Task Manager'[End Date])

Diff vs StartDates_measure = VALUE([Real StartDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule Start Date]))

Diff vs EndDates_measure = VALUE([Real EndDate_measure]-MAXX(FILTER(ALL('Project'),[ID Project]=SELECTEDVALUE('Task Manager'[ID Project])),[Schedule End Date]))

 

Result:

v-yuaj-msft_0-1606701524447.png

 

v-yuaj-msft_1-1606701524452.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User III
Super User III

Real StartDate = MINX(FILTER(ALL(TaskManager), TaskManager[ID Project] = EARLIER(TaskManager[ID Project])), TaskManager[Start Date])

 

Diff vs StartDates = TaskManager[Real StartDate] - RELATED(Projects[ScheduleStartDate])

 

Then end date would follow same pattern:

 

Real EndDate= MAXX(FILTER(ALL(TaskManager), TaskManager[ID Project] = EARLIER(TaskManager[ID Project])), TaskManager[End Date])

 

Diff vs EndDates = TaskManager[Real EndDate] - RELATED(Projects[ScheduleEndDate])


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hey, im trying to use this measure but it gives me an error with EARLIER function, is not letting me put any column there

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors