How can I combine year start data with year-round data?

Hi beginnerBee ,

 

You can achieve your desired outcome of ignoring the month filter for the beginning balance while respecting the month filter for the dynamic monthly sales by setting up disconnected slicer table like below.

I have attached an example pbix file for your reference.

 

Best regards,

Then you can write the fixed beginning balance like below:

Fixed Year Start Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    'Date'[Month Number] = 1, // Hardcoded filter for January
    ALL('Date'[Month-Year], 'Date'[Month Number]) // Ignores the row context
)

While the dynamic monthly sales measure showing up to the date of the slicer is as shown below:

Dynamic Monthly Sales = 
VAR SelectedDate = [Selected Reference Date]
RETURN
    // Usual adding up will produce incorrect total
    // Check if we are on a single row (like "2025-01") or the "Total" row
    IF (
        HASONEVALUE ( 'Date'[Month-Year] ),
        
        // --- LOGIC FOR A SINGLE ROW ---
        // (This is the simple logic that was already working)
        VAR CurrentMonthStartDate = MIN ( 'Date'[Date] )
        RETURN
            IF (
                CurrentMonthStartDate <= SelectedDate,
                SUM ( Sales[Amount] ),
                BLANK ()
            ),
            
        // --- LOGIC FOR THE TOTAL ROW ---
        // (Iterate over each month, apply the row logic, and sum the results)
        SUMX (
            VALUES ( 'Date'[Month-Year] ),
            
            // This CALCULATE forces the logic inside to be
            // evaluated for each specific month in the iteration
            CALCULATE (
                VAR CurrentMonthStartDate = MIN ( 'Date'[Date] )
                RETURN
                    IF (
                        CurrentMonthStartDate <= SelectedDate,
                        SUM ( Sales[Amount] ),
                        BLANK ()
                    )
            )
        )
    )

The resultant table respects your slicer from disconnected date table.

 

Best regards,