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

[beginnerBee]

I have two sets of DAX measures:

1. One set calculates the Year Start values based on a specific reference_date.

2. The other set calculates monthly values that dynamically change depending on the selected reference_date from a slicer.

My goal is to display both sets of values in a single table. However, I'm facing an issue: when I select a reference_date in the slicer other than the specific one used for the Year Start measures, those Year Start values disappear.

I’ve tried using REMOVEFILTERS(reference_date) in the Year Start measures, hoping it would ignore the slicer selection and always show the correct values. But that doesn’t seem to work — the slicer still affects the output.

How can I properly combine these two sets of measures in one table, ensuring that the Year Start values remain fixed (based on a specific reference_date) regardless of slicer selection, while the monthly values stay dynamic?

[v-nmadadi-msft]

Hi @beginnerBee 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @beginnerBee ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[DataNinja777 · Accepted solution]

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,