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

[BeenBee]

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

[johnt75]

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]
        )
    )

[BeenBee · Accepted solution]

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.