DAX Add Column from other Table

[rxt]

I have a calendar table example below.

Date	Year	start of Year	End of year
1/1/2021 12:00 AM	2021	1/1/2021 12:00 AM	12/31/2021 12:00 AM
1/2/2021 12:00 AM	2021	1/1/2021 12:00 AM	12/31/2021 12:00 AM
1/3/2021 12:00 AM	2021	1/1/2021 12:00 AM	12/31/2021 12:00 AM

And I also have a data table

ChangeID	Scheduled_Start_Date	Scheduled_End_Date	Scheduled_Start_Date_DATE_ONLY	Scheduled_End_Date_DATE_ONLY
CRQ000002864740	7/1/2021 12:30	7/1/2021 16:30	7/1/2021	7/1/2021
CRQ000002864852	7/1/2021 8:00	7/1/2021 14:00	7/1/2021	7/1/2021
CRQ000002910969	6/22/2021 23:00	7/9/2021 1:00	6/22/2021	7/9/2021

I want to add “Date” column from Calendar table  with a filter  as below to the data table

            //Time Segment Started Earlier and lasted AFTER duration of End Date

('Calendar'[Date]<[Scheduled_Start_Date] && 'Calendar'[Date]> [Scheduled_End_Date]) ||
            //Time Segment Started Earlier and lasted within duration of End Date
            ('Calendar'[Date] < DATEVALUE([Scheduled_Start_Date]) && ('Calendar'[Date] >= DATEVALUE([Scheduled_Start_Date]) && 'Calendar'[Date] <= DATEVALUE([Scheduled_End_Date])))
            ||
            //Time Segment started and ended within duration
            ('Calendar'[Date] >= DATEVALUE([Scheduled_Start_Date]) && 'Calendar'[Date] <= DATEVALUE([Scheduled_End_Date])) && ('Calendar'[Date] >= DATEVALUE([Scheduled_Start_Date]) && 'Calendar'[Date] <= DATEVALUE([Scheduled_End_Date]))
            ||
            //Time Segment Started within Duration but lasted after End Date
            ('Calendar'[Date] >= DATEVALUE([Scheduled_Start_Date]) && 'Calendar'[Date] <= DATEVALUE([Scheduled_End_Date])) && ('Calendar'[Date] > DATEVALUE([Scheduled_End_Date]))
        ))

And the result should look like this

dt	ChangeID	Scheduled_Start_Date	Scheduled_End_Date	Scheduled_Start_Date_DATE_ONLY	Scheduled_End_Date_DATE_ONLY
7/1/2021 0:00	CRQ000002864740	7/1/2021 12:30	7/1/2021 16:30	7/1/2021	7/1/2021
7/1/2021 0:00	CRQ000002864852	7/1/2021 8:00	7/1/2021 14:00	7/1/2021	7/1/2021
7/1/2021 0:00	CRQ000002910969	6/22/2021 23:00	7/9/2021 1:00	6/22/2021	7/9/2021
7/1/2021 0:00	CRQ000002925233	6/28/2021 0:30	7/5/2021 2:30	6/28/2021	7/5/2021
7/1/2021 0:00	CRQ000002926979	7/1/2021 1:00	7/1/2021 4:00	7/1/2021	7/1/2021
7/1/2021 0:00	CRQ000002928127	6/27/2021 21:30	7/2/2021 12:30	6/27/2021	7/2/2021

I tried ADDCOLUMN or SUMMERIZE to add the column but not sure how to use the filter  in the row context like this. If I use aggregate functions, it allows the filter, but I won’t be able to get the date field.

any idea if this can be achieved ? I would appriciate your assitance

Thanks

RXT

[colacan · Accepted solution]

Hi RXT,

After creating a calendar table;

Date = CALENDARAUTO()

Please try below

ResultTable =

    Filter(
        CROSSJOIN( VALUES( Date[Date]), DataTable ),
        [Date] >= [Start_Date]  &&
        [Date] <= [End_Date]
    ) 

Hope this helps you. Thanks

[rxt]

Hi Colacan, the CROSSJOIN FILTER did the trick. Many thanks!!! you are fantastic👍