Power BI DAX: Calendar Table Integration Breaking Existing Measure Logic

[julsr]

Hi PowerBI Community,

I have an interesting DAX challenge involving the integration of a new Calendar table into an existing model. Here's my scenario:

I have three tables:

1. A fact table (TABLE1) with transaction dates and client IDs
2. A budget table (TABLE2) with monthly amounts, linked to TABLE1 through a YearMonth concatenated field (format: YYYYMM)
3. A newly added Calendar table linked to TABLE1 through date relationships

Previously, I had a measure that calculated remaining budget amounts for the year based on a date slicer connected directly to TABLE1's date field. The measure worked perfectly. However, after introducing the Calendar table as my master date table (with the slicer now connected to Calendar), the measure returns blank values.

Here's my original working measure (masked for privacy):

Measure_Name = 
VAR CurrentDate = MAX(TABLE1[DATE_FIELD])
VAR YearStartDate = DATE(YEAR(CurrentDate), 1, 1)
VAR CurrentMonth = MONTH(CurrentDate)
RETURN
CALCULATE(
SUM(TABLE2[AMOUNT_FIELD]),
FILTER(
CROSSJOIN(
ALL(TABLE1[DATE_FIELD]),
VALUES(TABLE1[CLIENT_ID])
),
MONTH(TABLE1[DATE_FIELD]) > CurrentMonth &&
YEAR(TABLE1[DATE_FIELD]) = YEAR(CurrentDate)
)
)

The measure calculates future month budgets based on the selected date. It worked when the slicer was connected directly to TABLE1[DATE_FIELD], but now that I'm using a Calendar table for date selection, it's not working.

My table relationships are:

• Calendar[Date] -> TABLE1[DATE_FIELD]
• TABLE1[YEARMONTH] -> TABLE2[YEARMONTH]

How can I modify this measure to work with the Calendar table while maintaining the same calculation logic?

Below is sample data:

• https://drive.google.com/file/d/1mDdm9CmTebgXARfgseHC4ZGC5MrTx7rJ/view
• https://drive.google.com/file/d/1VsYOketE5NOmmNkLPiRFXHA6Y0FUUJLx/view
• https://drive.google.com/file/d/18udRPZns2Vv5f3tBUPGsRAwitG5R8ECm/view

Any help would be greatly appreciated!

[Ashish_Mathur]

Hi,

I do not know what you want to calculate.  In the attached file, i have set up the tables, relationships and slicers properly.  Build your measures now.

Hope this helps.

[julsr]

Thanks! Attached is the PBIX file and the measure. The expected result is a measure that once the filter from the calendar table is active returns the sum of the budget from the budget amount field where budget Date > CAlendar date. (i.e. if February is selected in the calendar slicer, the result must be the sum of the budgets from March and April, 384,000)

https://drive.google.com/file/d/1hRrtOCUvIX_uVUj96yBAE0ws2H0IjE9O/view?usp=sharing

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Revise the measure to

measure_column = CALCULATE(SUM(Budget[AMOUNT_FIELD]),DATESBETWEEN('Calendar'[Date],MAX('Calendar'[Date])+1,MAXX(ALL('Calendar'),'Calendar'[Date])))

Hope this helps.

[julsr]

Thanks! It's working now. 

[Ashish_Mathur]

You are welcome.

[julsr]

Hi again! I missed one thing - the relationship between the tables. I've updated the PBIX file to reproduce the same error

https://drive.google.com/file/d/1hRrtOCUvIX_uVUj96yBAE0ws2H0IjE9O/view?usp=drive_link

I created measure_field2 using the code you sent me and I'm using the Power BI functionalities I need (Single Date picker and Top n 1 ordered by date), but the code you provided isn't returning the expected results. The relationship must remain as it is currently configured, which is why I cannot implement it differently. I've tried modifying it, but doing so breaks my report.

Could you provide additional guidance considering this table relationship structure?

[Ashish_Mathur]

If you insist on that relationship staying intact then i will not be able to help you because tht relationship should not exist in the first place.