topic Re: How can I combine year start data with year-round data? in DAX Commands and Tips

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

beginnerBee — Tue, 21 Oct 2025 13:01:33 GMT

I have two sets of DAX measures:

One set calculates the Year Start values based on a specific reference_date.
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?

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

DataNinja777 — Tue, 21 Oct 2025 15:47:27 GMT

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,

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

Anonymous — Thu, 23 Oct 2025 10:24:59 GMT

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.

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

Anonymous — Mon, 27 Oct 2025 04:49:32 GMT

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