Forum Discussion
Helper IIICalculated measure based on date from related table
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 |
I ended up redesigning my query and tables so this is not valid anymore.
8 Replies
- rajendraongole1
Super User
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!! 
bigkHello, 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!!
- 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?
- bigk
I ended up redesigning my query and tables so this is not valid anymore.
