Forum Discussion
DAX Add Column from other Table
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
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
2 Replies
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
Hi Colacan, the CROSSJOIN FILTER did the trick. Many thanks!!! you are fantastic👍
