DAX/Power Pivot：基于累计总成本计算75%支出日期及EAC问题咨询

Got it, let's walk through how to figure out that 75% spend date at the Account level, using your EAC-filtered cost tables. Here's a step-by-step approach tailored to your setup:

First off, make sure all three of your cost tables (Forecasted Cost, Actual Costs, Invoiced Costs) have consistent Account and Date columns. For example, Forecasted should have a forecast date, Actual an actual spend date, and Invoiced an invoice date. If any table is missing dates, you'll need to fill those in—dates are critical for tracking cumulative spend over time. Also, confirm the EAC Filter column is applied at the right level (whether per Account+Date or just per Account) since that will affect our filtering logic later.

We’ll start by merging all three tables into one, but only include rows where EAC Filter is set to "Y". This simplifies our cumulative calculations later. Use this DAX formula to create a calculated table:

EAC_Combined_Costs = 
// Union all three cost tables with standardized columns
VAR CombinedRaw = 
    UNION(
        SELECTCOLUMNS(Forecasted_Cost, 
            "Account", Forecasted_Cost[Account],
            "Cost_Date", Forecasted_Cost[Forecast_Date],
            "Cost_Amount", Forecasted_Cost[Cost],
            "EAC_Filter", Forecasted_Cost[EAC Filter]
        ),
        SELECTCOLUMNS(Actual_Costs, 
            "Account", Actual_Costs[Account],
            "Cost_Date", Actual_Costs[Actual_Date],
            "Cost_Amount", Actual_Costs[Cost],
            "EAC_Filter", Actual_Costs[EAC Filter]
        ),
        SELECTCOLUMNS(Invoiced_Costs, 
            "Account", Invoiced_Costs[Account],
            "Cost_Date", Invoiced_Costs[Invoice_Date],
            "Cost_Amount", Invoiced_Costs[Cost],
            "EAC_Filter", Invoiced_Costs[EAC Filter]
        )
    )
// Filter only rows where EAC Filter is "Y"
RETURN FILTER(CombinedRaw, CombinedRaw[EAC_Filter] = "Y")

Quick note: If your EAC Filter is only set per Account (not per Account+Date), you can adjust the filter to check the Account's status directly instead of bringing the filter into the union.

Next, create a measure to track the running total of costs for each Account, ordered by date. This will show us how much has been spent (or forecasted/invoiced) up to each date:

Cumulative_EAC_Cost = 
CALCULATE(
    SUM(EAC_Combined_Costs[Cost_Amount]),
    // Keep all dates up to the current date in the filter context
    FILTER(
        ALLSELECTED(EAC_Combined_Costs[Cost_Date]),
        EAC_Combined_Costs[Cost_Date] <= MAX(EAC_Combined_Costs[Cost_Date])
    ),
    // Keep the current Account fixed
    ALLEXCEPT(EAC_Combined_Costs, EAC_Combined_Costs[Account])
)

We need the full total EAC cost to find our 75% threshold. This measure gives us the sum of all EAC-eligible costs per Account:

Total_EAC_Cost = 
CALCULATE(
    SUM(EAC_Combined_Costs[Cost_Amount]),
    ALLEXCEPT(EAC_Combined_Costs, EAC_Combined_Costs[Account])
)

Finally, this measure will return the earliest date where the cumulative cost reaches or exceeds 75% of the total EAC cost for the Account:

75%_Spend_Date = 
VAR Target_Threshold = [Total_EAC_Cost] * 0.75
// Create a table of dates with their corresponding cumulative costs
VAR Cumulative_Date_Table = 
    ADDCOLUMNS(
        VALUES(EAC_Combined_Costs[Cost_Date]),
        "Running_Total", [Cumulative_EAC_Cost]
    )
// Filter dates where cumulative cost meets or exceeds the 75% threshold
VAR Eligible_Dates = 
    FILTER(Cumulative_Date_Table, [Running_Total] >= Target_Threshold)
// Return the earliest eligible date, or blank if threshold isn't met
RETURN
    IF(
        NOT ISBLANK(Target_Threshold) && COUNTROWS(Eligible_Dates) > 0,
        MINX(Eligible_Dates, EAC_Combined_Costs[Cost_Date]),
        BLANK()
    )

• Date Formatting: Double-check that your Cost_Date column is formatted as a date type (not text) so the ordering works correctly—this ensures we get the earliest possible date for the 75% threshold.
• Dynamic EAC Updates: Since your EAC Filter column changes automatically, all these measures will refresh dynamically too. No manual updates needed as data or filter values change.
• Budget Comparison: If you want to compare this 75% date to your Account-level budget, you can create a similar cumulative budget measure and cross-reference the two dates to track variance.

内容的提问来源于stack exchange，提问作者Brian