Calculated measure based on date from related table

[bigk]

Hello folks

I’ve run into problems with calculated measures. I have 3 tables with meeting dates, projects and tasks. The relationship is one-to-many in the following path - Meeting dates->Projects->Tasks

My aim is to make a calculated measure “completed tasks” that would report for the current meeting date the total number of closed tasks since last meeting date. E.g. all tasks that are closed after 2020-07-22 but before 2022-10-25 should be reported to 2022-10-25 meeting date. But i need to use meeting date table instead of dates from projects table because there may be more meeting dates than projects. And tasks can be closed after project dates.

The same meeting date table should be used as a filter to filter projects and other related info down the relationship path when i build visuals. For example started projects as of meeting date and related tasks (total, not closed).

I’ve run out of ideas how to make a measure. Because of meeting-project relationship and consequently project-task relationship, the closed tasks measure is being filtered too. I’ve tried to use variety of calculations elements like remove filters, allselected, etc. but seems that those are overridden by relationship. And I need that relationship because of reporting needs.

I’ve made a calculated column for for each task to identify the meeting date and made an inactive relationship to meetings table. Then used USERELATIONSHIP but it did not work as projects are filtering away tasks.

Please help me as i've spend too much time on this. 

Thanks, folks.

Meeting dates

Meeting date

2018-10-22

2018-07-24

2020-07-22

2019-04-23

Projects

Project ID	Meeting date
2019-04	2019-04-23
2020-06	2020-07-22
2022-15	2022-10-25
2023-16	2024-01-29

Tasks

Project	Task	Comletion date
2019-04	1	2020-07-22
2019-04	2	2020-07-22
2020-06	1	2022-10-25
2020-06	5	2024-01-29
2022-15	6	2024-01-29

[bigk · Accepted solution]

I ended up redesigning my query and tables so this is not valid anymore.

[bigk]

Unfortunately not working but we are closer :). We've got one value. 
You can see that there is only 1 completed task between 19.12.2023 and 29.01.2024.

I've made a slicer to filter completed tasks straight from the tasks table and you can see there are 5 of them. I've unlinked slicer from the left table. Count of projects and tasks seems to be reporting OK as those are hardlinked through relationship but measure is not. And there is also total missing which looks to me wierd.

[rajendraongole1]

Hi @bigk - Ok, i was checking on that only, can you try below one.

Completed Tasks =
VAR CurrentMeetingDate = SELECTEDVALUE('Meeting Dates'[Meeting Date])
VAR PreviousMeetingDate =
CALCULATE(
MAX('Meeting Dates'[Meeting Date]),
FILTER(
ALL('Meeting Dates'),
'Meeting Dates'[Meeting Date] < CurrentMeetingDate
)
)
VAR TasksBetweenDates =
CALCULATETABLE(
'Tasks',
'Tasks'[Completion Date] > PreviousMeetingDate &&
'Tasks'[Completion Date] <= CurrentMeetingDate
)
RETURN
COUNTROWS(TasksBetweenDates)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[bigk]

Still same results. 

Maybe there is some way to share my pbix to you or it won't help?

[Anonymous]

Hi @bigk 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

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

[bigk]

Hello, @rajendraongole1 

Thanks for very quick response. I've tried your suggestions. First of all i think that relationship between meeting dates table and tasks should be present based on your suggestions. So i've made it.

But unfortunately i'm not getting any results if i report it on meeting date level or on project level.

[rajendraongole1]

Hi @bigk - Ok, can you try the below measure with out using the userrelationship function to get the

completed tasks between the previous and current meeting dates.

Completed Tasks =
VAR CurrentMeetingDate = SELECTEDVALUE('Meeting Dates'[Meeting Date])
VAR PreviousMeetingDate =
CALCULATE(
MAX('Meeting Dates'[Meeting Date]),
FILTER(
ALL('Meeting Dates'),
'Meeting Dates'[Meeting Date] < CurrentMeetingDate
)
)
RETURN
CALCULATE(
COUNTROWS('Tasks'),
FILTER(
'Tasks',
'Tasks'[Completion Date] > PreviousMeetingDate &&
'Tasks'[Completion Date] <= CurrentMeetingDate
)
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

Hi @bigk - Can you try the below measure calculation for completed tasks

Completed Tasks =
VAR CurrentMeetingDate = SELECTEDVALUE('Meeting dates'[Meeting date])
VAR PreviousMeetingDate =
CALCULATE(
MAX('Meeting dates'[Meeting date]),
'Meeting dates'[Meeting date] < CurrentMeetingDate
)
RETURN
SUMX(
'Projects',
CALCULATE(
COUNTROWS('Tasks'),
'Tasks'[Completion date] > PreviousMeetingDate && 'Tasks'[Completion date] <= CurrentMeetingDate,
USERELATIONSHIP('Tasks'[Completion date], 'Meeting dates'[Meeting date])
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!