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