Power BI预算值按日复制及多公司筛选度量开发问题

Hey there, let's tackle this budget value expansion and filtering problem you're facing. First, let's recap the context clearly:

Context #

You've got three tables: Clients, Calendar, Budget. ClientUK is a calculated column combining ClientID and Company, and a key rule is same client across multiple companies shares the same BU.

Your core need: Budget values only exist on month-end dates, and you need to replicate this value to every day of the month for weighted average calculations. But your current DAX measures have limitations:

• One measure only works for single company selection
• Another ignores company filters and returns total budget across all companies
• Attempts at dynamic branching caused a True/False expression without a specific column error, and even when fixed, it fails when selecting partial companies (e.g., 2 out of 3) while also being hard to maintain for more filter dimensions
• LOOKUPVALUE isn't an option since you need sum support for multi-select/all-select scenarios
• Duplicating Budget rows is impossible due to potential massive data bloat from tens of thousands of existing rows

Problematic Measures You've Tried #

Measure 1: Works for single company but no multi-select #

BDG = VAR SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR SearchBU = SELECTEDVALUE(Clients[BU])
VAR SearchCompany = SELECTEDVALUE(Clients[Company])
RETURN CALCULATE ( SUM ( Budget[ValueBDG] );
FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = SearchDate );
FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = SearchBU );
FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = SearchCompany );
ALL ( Budget ) )

Measure 2: Ignores company filters #

BDG = VAR _SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR _SearchBU = SELECTEDVALUE(Clients[BU])
--VAR _SearchCompany = SELECTEDVALUE(Clients[Company])
--VAR _FilterCompany = FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = _SearchCompany )
RETURN CALCULATE ( SUM ( Budget[ValueBDG] );
FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = _SearchDate );
FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = _SearchBU );
--IF(_SearchCompany = BLANK(); ALL(Clients[Company]); _FilterCompany);
ALL ( Budget ) )

Measure 3: Branching attempt that fails on partial company selection #

BDG = VAR _SearchDate = EOMONTH ( SELECTEDVALUE ( Calendar[Date] ); 0 )
VAR _SearchBU = SELECTEDVALUE ( Clients[BU] )
VAR _SearchCompany = SELECTEDVALUE ( Clients[Company] )
VAR _FilterCompany = FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = _SearchCompany )
RETURN IF ( _SearchCompany = BLANK ();
CALCULATE ( SUM ( Budget[ValueBDG] );
FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = _SearchDate );
FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = _SearchBU );
ALL ( Clients[Company] );
ALL ( Budget ) );
CALCULATE ( SUM ( Budget[ValueBDG] );
FILTER ( ALLNOBLANKROW ( Calendar[Date] ); Calendar[Date] = _SearchDate );
FILTER ( ALLNOBLANKROW ( Clients[BU] ); Clients[BU] = _SearchBU );
KEEPFILTERS ( _FilterCompany );
ALL ( Budget ) ) )

The issue here is that when selecting partial companies, it still returns the total across all companies, plus branching logic becomes a maintenance nightmare with more filter dimensions.

Solution: Context-Aware DAX Measure #

The key is to preserve the existing filter context for BU and Company, while only overriding the date filter to target the month-end date—no branching needed, and it works for all selection scenarios:

BDG = 
VAR _CurrentMonthEnd = EOMONTH(MAX(Calendar[Date]), 0)
RETURN
CALCULATE(
    SUM(Budget[ValueBDG]),
    -- Filter Budget to the month-end date matching the current context
    Calendar[Date] = _CurrentMonthEnd,
    -- Keep all existing filters on the Clients table (BU, Company, etc.)
    KEEPFILTERS(Clients),
    -- Clear original date filters to ensure we only pull month-end budget data
    ALL(Calendar)
)

How This Works #

1. _CurrentMonthEnd: Uses MAX(Calendar[Date]) to get the month-end date for the current date filter context—works better than SELECTEDVALUE for multi-date selections.
2. Calendar[Date] = _CurrentMonthEnd: Directly targets the budget value stored on the month-end date.
3. KEEPFILTERS(Clients): Automatically retains all active filters on Clients[BU] and Clients[Company], whether you select one, multiple, or all companies/BUs.
4. ALL(Calendar): Removes any existing date filters so the measure doesn't get stuck on non-month-end dates in your visual.

This approach avoids data duplication, requires no conditional branching, and seamlessly supports all your filter scenarios without maintenance overhead.

内容的提问来源于stack exchange，提问作者Black Rock Shooter