Forum Discussion
Issue with Completed Projects Count Measure for Year-Month Line Chart
Hello,
I need help creating a measure to accurately count completed projects in a Year-Month line chart. Here's a sample of my data from the fact table:
Project Date To Client Employee Status StatusBasedOnMaxDateTimeColumn
| ProjectA | 01-10-2024 | 08:00 | CEA | Lewis | In Progress | Completed |
| ProjectA | 05-10-2024 | 17:32 | CEA | Lewis | Completed | Completed |
| ProjectA | 15-11-2024 | 19:00 | JR | Vettel | In Progress | Completed |
| ProjectA | 15-11-2024 | 20:00 | JR | Vettel | Completed | Completed |
| ProjectB | 02-10-2024 | 06:04 | RAC | Leclerc | In Progress | In Progress |
| ProjectB | 03-10-2024 | 13:31 | RAC | Leclerc | Completed | In Progress |
| ProjectB | 19-10-2024 | 01:15 | DOC | Max | In Progress | In Progress |
| ProjectB | 01-11-2024 | 19:00 | DOC | Max | In Progress | In Progress |
Explanation:
- I have a calculated column, StatusBasedOnMaxDateTimeColumn, which returns the overall status of a project based on the maximum datetime entry for that project.
- For example, since the latest status for ProjectA is "Completed," all entries for ProjectA show "Completed" in the calculated column.
- This calculated column ensures that the StatusBasedOnMaxDateTimeColumn column always reflects the final status of a project.
Current Measure:
Completed Projects Count =
CALCULATE(
DISTINCTCOUNT('T1'[Project]),
FILTER(
'T1',
[StatusBasedOnMaxDateTimeColumn] = "Completed"
)
)Issue:
- The above measure works perfectly in a card visual, and the count matches the count of "Completed" status in a table visual.
- However, when I use this measure in a Year-Month line chart, the same project is counted multiple times if it appears across different months.
- For example, ProjectA is counted as completed in October 2024 and again in November 2024.
- The goal is to aggregate the completed project count in the line chart so it matches the "Completed" status count in the table visual.
Additional Requirements:
- The measure should respect slicers for Client, Employee, and Project, but it should count projects only once based on the final status, regardless of the month where it appears.
Expected output in the line chart:Overall(without Selection) Year Month Completed Projects Count 2024 oct 2024 Nov 1 Total 1 If Client:CEA Selected Year Month Completed Projects Count 2024 oct 2024 Nov 1 Total 1 If Client:JR Selected Year Month Completed Projects Count 2024 oct 2024 Nov 1 Total 1
Table visual:I have also attached the PBIX file with sample data.
https://drive.google.com/drive/folders/1BgYc53Bvcz2OHlg61EeXY5bRWoR2l06XAny guidance on how to adjust my measure to achieve this would be greatly appreciated!
Thank you!
- The measure should respect slicers for Client, Employee, and Project, but it should count projects only once based on the final status, regardless of the month where it appears.
5 Replies
Hi Anonymous
If I understand your problem correctly, you should just be able to add a visual level filter to your line chart with the condition Status = "Completed" (the original status column not your calculated column) and that should result in a line chart that only counts projects that are completed, in the month they were completed.
Alternatively, if you need to have the "latest status" and not just the completed ones, you could create an other calculated column called Latest Status Date that would for each project look up the Date on which Status = StatusBasedOnMaxDateTimeColumn, and then use this Latest Status Date as your chart axis instead of the original Date. Let me know if you need help with the DAX for this. 🙂
Best regards,
DanielHi dk_dk
Thanks for your reply, the half of the issue is solved , i created latest date column and am used a logic to find latest date and am using that in my visual this is working fine but here the issue is i have Client,Employee,Project as slicers
for exampleProject Date To Client Employee Status StatusBasedOnMaxDateTimeColumn Latest Date ProjectA 01-10-2024 08:00:00 CEA Lewis In Progress Completed ProjectA 05-10-2024 17:32:00 CEA Lewis Completed Completed ProjectA 15-11-2024 19:00:00 JR Vettel In Progress Completed ProjectA 15-11-2024 20:00:00 JR Vettel Completed Completed 15-11-2024 ProjectB 02-10-2024 06:04:00 RAC Leclerc In Progress In Progress ProjectB 03-10-2024 13:31:00 RAC Leclerc Completed In Progress ProjectB 19-10-2024 01:15:00 DOC Max In Progress In Progress ProjectB 01-11-2024 19:00:00 DOC Max In Progress In Progress
if i select projectA in the slicer its working fine , if i select client JR it will working fine ,but i need to show something like even if we select CEA(client) also i should show as the project is completed ,the count should be in the Nov 2024 like same for Employee slicer
(i.e) under single project we have multiple clients and vice-versa,under single project we have multiple employees also
the ultimate goal is to match the completed count which present in the table visual should match with the line chart even if we aggreate the count alsoAh I see,
So basically you want 15-11-2024 to be the value in the first 3 rows of your sample data as well (in the Latest Date column)?
Could you share the DAX you used for Latest Date?
Best regards,
Daniel
Hi Anonymous
Thank you for being part of the Microsoft Fabric Community.
As highlighted by dk_dk , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.
Best Regards,
Cheri SrikanthHi Anonymous
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
