DISTINCTCOUNT with Condition, filtered based on latest update

[LaelTejo]

Hello. I have created a report for Project Progress Update.

One of the highlight is the # of Project Completed, # of Project On Progress, and # of Project Not Started.

Considering on their importance, I am making an individual card visualization for each. I use the following DAX for January report:

# of On Progress = CALCULATE(DISTINCTCOUNT(Table1[Project Name]), Table1[Project Status] IN {On Progress})

It works when the data only contains the information for January. The data, however, is separated into months and is appended every 25th. Therefore, it would inflate the number for each status.

I want it to only count the LATEST Update for that project. Imagine the data look like this:

Reporting Date	Project Name	Project Status
25-Jan-24	Project A	On Progress
25-Jan-24	Project B	Not Started
25-Jan-24	Project C	On Progress
25-Jan-24	Project D	Completed
25-Feb-24	Project A	Completed
25-Feb-24	Project B	Not Started
25-Feb-24	Project C	On Progress
25-Feb-24	Project E	On Progress
25-Mar-24	Project B	On Progress
25-Mar-24	Project C	On Progress
25-Mar-24	Project E	Completed
25-Mar-24	Project F	Not Started
25-Mar-24	Project G	On Progress

Project D is completed on Jan, so it does not show up any other time, and I can use the usual DAX for Completed. The other ones, however, still reports changed. I want Project B to be calculated on the "On Progress" card and not on the "Not Started" card.

If I were to open the report after 25 March, the data will show 3 On Progress, 3 Completed, and 1 Not Started.

Please help me create the measure to support this. I have scoured this forum, and am unable to find the exact method to calculate with DISTINCTCOUNT. If possible, I would like to avoid needing to add a column to the data.

Thank you.

[123abc · Accepted solution]

To achieve the desired result of counting the latest status of each project, you can use the following DAX measure:

# of On Progress Latest =
VAR LatestReportingDate =
CALCULATE(
MAX(Table1[Reporting Date]),
ALLEXCEPT(Table1, Table1[Project Name])
)
RETURN
CALCULATE(
DISTINCTCOUNT(Table1[Project Name]),
FILTER(
Table1,
Table1[Project Status] = "On Progress" &&
Table1[Reporting Date] = LatestReportingDate
)
)

This measure calculates the distinct count of project names where the project status is "On Progress" and the reporting date matches the latest reporting date for each project.

Here's how the measure works:

1. VAR LatestReportingDate: This variable calculates the maximum reporting date for each project, removing all filters except the project name. This ensures that we isolate the latest reporting date for each project.

2. RETURN: This is where the calculation is performed. It calculates the distinct count of project names where the project status is "On Progress" and the reporting date matches the latest reporting date calculated in the first step.

This measure will dynamically adjust to the latest reporting date for each project, ensuring that only the latest status is counted.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[LaelTejo]

It works!

Thank you.

I am quite new to this, and I did not realize we can make a new VAR in DAX. Thanks for the help!