Issue with Completed Projects Count Measure for Year-Month Line Chart

[Anonymous]

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/1BgYc53Bvcz2OHlg61EeXY5bRWoR2l06X 
      
      

      Any guidance on how to adjust my measure to achieve this would be greatly appreciated!

      Thank you!

      
      
      
      
  
  
  
  
  
  

[v-csrikanth]

Hi @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.

[v-csrikanth]

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 Srikanth

[dk_dk]

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]

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

Project	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 also

[dk_dk]

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