Cumulative measure on virtual table

DAX
1

Good day,
I’m trying to solve what I thought was a straightforward cumulative total calculation. I spent quite a bit of time using ChatGPT to try to troubleshoot the issue until it finally told me to post to a forum and ask the experts, hence I’m here.

I’ve attached my model for reference.

The fact table is straightforward. I have a list of Purchase Orders with a milestone indicating when the PO was either issued, or the supplier was given a notice to proceed. For each PO and milestone there are four dates, Schedule Date, Actual, Forecast and Projected.

POTableSample
POTableSample889×239 9.21 KB

I’m trying to create a visual that shows the cumulative total of Purchase Orders by month, Scheduled and Actual.

POTableVisual

I’ve created the CumulativeCountByScheduleDate using the following pattern:

CumulativeCountByScheduleDate = 
CALCULATE(
    COUNT(POPerformance[PONumber]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    ),
    USERELATIONSHIP(POPerformance[Schedule Date], 'Date'[Date])
)

This seems to be working fine and giving me the expected result.

Since the Actual dates are not in ascending order in the table as they could be early or late compared to the Schedule Date, I attempted to create the second measure, CumulativeCountActual, by using a virtual table and then applying the cumulative count pattern to that table.

CumulativeCountActual = 
VAR ActualDates =
    SUMMARIZE(
        FILTER(POPerformance, POPerformance[Actual] <> BLANK()),
        POPerformance[PONumber],
        POPerformance[Actual],
        "Count", COUNT(POPerformance[Actual])
    )

VAR CountPurchOrders = COUNTROWS(ActualDates)

RETURN
    CALCULATE(
        CountPurchOrders,
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= MAX('Date'[Date])
        ),
        USERELATIONSHIP(POPerformance[Actual], 'Date'[Date])
    )

This measure was the result of multiple debugging efforts between myself and the ChatGPT bot.

As you can see from the above image of the table visual, I’m only getting the count of actualized POs and not a cumulative total.

I would be grateful for any guidance on how I might solve this issue. Thanks in advance for any assistance, and please let me know if any further clarification is needed.

Best regards,

George
CumulativeActual_20231109.pbix (52.9 KB)