Data modeling with multiple date columns

[Anonymous]

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

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

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:

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.

[AllisonKennedy]

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])

[Anonymous]

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