DAX mesure depens on calendar and dates

[Anonymous]

Hi, 
I have two tables and Calendar_table

Table 1

Project	Site	Start date	End date	Duration
1122	12	01.05.2023	10.07.2023	10
1122	12	11.07.2023	02.09.2023	8
1122	12	03.09.2023	13.12.2023	14
1133	13	02.02.2023	02.06.2023	17
1133	13	03.06.2023	12.09.2023	14
1133	13	13.09.2023	15.11.2023	9
1133	14	01.04.2023	25.06.2023	12
1133	14	26.06.2023	05.10.2023	14
1133	14	06.10.2023	13.12.2023	10

Table 2

Person	Project	Site	Start date appointment site	End dateappointment site
Name	1122	12	01.05.2023	10.09.2023
Name	1133	13	02.02.2023	13.09.2023
Name	1133	14	01.04.2023	26.06.2023
Name2	1122	12	11.09.2023
Name2	1133	13	14.09.2023
Name2	1133	14	26.06.2023

Table 2 has many-to-many connection with Table 1, where Table 2 ---> Table 1 (not both) by Site

I need mesure depens on Calendar_table - which returns actual Duration (period Start Date-End Date) and actual appointment Site for Person. 

For example, 

Choose in Calendar_table - March 2023
Name - Site 13 - returns 17
Name2 - Site 13 - returns null

Calendar_table - November 2023
Name - Site 13 - returns null
Name2 - Site 13 - returns 9

[DataNinja777 · Accepted solution]

Hi @Anonymous ,

To calculate the actual Duration for each Person and Site based on the Calendar_table date selection, we can use DAX to:

1. Filter the data for the selected date range.
2. Intersect the appointment period with the project duration period.
3. Return the overlapping duration for each person and site.

Steps to Create the Measure

Here’s a DAX measure to compute the Actual Duration:

Actual Duration = 
VAR SelectedDate = MAX(Calendar_table[Date]) -- The selected date from the Calendar table
VAR AppointmentPeriod = 
    FILTER(
        Table2,
        Table2[Start date appointment site] <= SelectedDate &&
        (ISBLANK(Table2[End dateappointment site]) || Table2[End dateappointment site] >= SelectedDate)
    )
VAR ProjectPeriod = 
    FILTER(
        Table1,
        Table1[Start date] <= SelectedDate &&
        Table1[End date] >= SelectedDate
    )
VAR MatchingPeriods =
    FILTER(
        GENERATE(AppointmentPeriod, ProjectPeriod),
        Table2[Project] = Table1[Project] &&
        Table2[Site] = Table1[Site]
    )
RETURN
    SUMX(MatchingPeriods, Table1[Duration])

Expected Output

• March 2023
• Name → Site 13 → Returns 17 (Project active for Name, duration overlaps with Calendar_table).
• Name2 → Site 13 → Returns null (No overlapping duration for the date).
• November 2023
• Name → Site 13 → Returns null (Appointment ended for the date).
• Name2 → Site 13 → Returns 9 (Project active for Name2 in November 2023).

Best regards,

[Anonymous]

Thanks a lot! It's work!