create column in new table based on a conditional select in another table

Hi,

I am not sure how your expected result looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In the sample, I tried to use GENERATE DAX Function to create a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

DateTable = 
GENERATE (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2024, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month Number", MONTH ( [Date] )
    ),
    SELECTCOLUMNS (
        FILTER (
            FileSemesters,
            ( FileSemesters[StartDate] <= [Date]
                && FileSemesters[EndDate] >= [Date] )
        ),
        "TermOfYear", FileSemesters[Semesters]
    )
)

JG_Austra 

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2016, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Semester",
VAR currentDate = [Date]
RETURN
MAXX(
FILTER(
FileSemesters,
FileSemesters[StartDate] <= currentDate &&
FileSemesters[EndDate] >= currentDate
),
FileSemesters[Semester]
)
)

You can then use this DateTable in your model to relate dates to semesters seamlessly.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn