Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 S...
Need Help
dk_dk's avatar
dk_dk
Super User
1 year ago

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,
Daniel

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi 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 example

ProjectDateToClientEmployeeStatusStatusBasedOnMaxDateTimeColumnLatest Date
ProjectA01-10-202408:00:00CEALewisIn ProgressCompleted 
ProjectA05-10-202417:32:00CEALewisCompletedCompleted 
ProjectA15-11-202419:00:00JRVettelIn ProgressCompleted 
ProjectA15-11-202420:00:00JRVettelCompletedCompleted15-11-2024
ProjectB02-10-202406:04:00RACLeclercIn ProgressIn Progress 
ProjectB03-10-202413:31:00RACLeclercCompletedIn Progress 
ProjectB19-10-202401:15:00DOCMaxIn ProgressIn Progress 
ProjectB01-11-202419:00:00DOCMaxIn ProgressIn 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 also

 

  • dk_dk's avatar
    dk_dk
    Super User
    1 year ago

    Ah 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