topic Re: Adding Calculated Column to Date Table based on Date Ranges in Another table in DAX Commands and Tips

Adding Calculated Column to Date Table based on Date Ranges in Another table

BeenBee — Wed, 15 Jan 2025 15:23:38 GMT

Hi All,

I am looking for some help adding a column to my date table based on date start and end found in another table.

What I am trying to do is to add a column to my date table which specifies for each date in the table, which semester it falls into. The dates for each semester can be found in the Semester Date table. There is a start and end date for each semester.

I have tried to adapt the solution found in another post. The issue I am having is that the column is currently only returning the final semester found in the Semester Date table.

The DAX I have is

Semester Date = VAR _a = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( MAX( 'Semester Dates'[Academic Semester]), FILTER ( ALL ( 'Semester Dates' ), 'Semester Dates'[Start]>= _a && _a <= 'Semester Dates'[End] ) )

Date Table;

Semester Date Table;

Any help would be greatly appreciated.

Many thanks

Re: Adding Calculated Column to Date Table based on Date Ranges in Another table

johnt75 — Wed, 15 Jan 2025 16:16:15 GMT

SELECTEDVALUE won't work in a calculated column, you can use

Semester Date = VAR _a = 'Date'[Date] RETURN CALCULATE ( MAX( 'Semester Dates'[Academic Semester]), FILTER ( ALL ( 'Semester Dates' ), 'Semester Dates'[Start]>= _a && _a <= 'Semester Dates'[End] ) )

Re: Adding Calculated Column to Date Table based on Date Ranges in Another table

BeenBee — Wed, 15 Jan 2025 16:36:51 GMT

Thank you very much for your response. I have made an adjustement to my original code and have had success with the SELECTEDVALUE

Semester Date = VAR _a = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE( MAX('Semester Dates'[Academic Semester]), FILTER( 'Semester Dates', [Date] >= 'Semester Dates'[Start] && [Date] <= 'Semester Dates'[End] ) )

I aslo found some issues with the dates in my Semester Dates Table, which I have addressed and is working correctly now.

Thank you for the help.