How to match date to a range of dates and count number of days

[Anonymous]

Hello,

I have the following two tables. The first table has ranges of dates which correspond to the shift dates of each person. Each shift date range is always continuous, for example, from 3 January 2022 to 7 January 2022, from 11 January 2022 to 15 January 2022, etc.

Work Shift Date	Name	Value	Submissions	Feedback
03 January 2022	Lizui	1	4	2
04 January 2022	Lizui	1	7	5
05 January 2022	Lizui	1	8	8
06 January 2022	Lizui	1	5	6
07 January 2022	Lizui	1	7	5
11 January 2022	Tegalpapak	1	8	8
12 January 2022	Tegalpapak	1	5	6
13 January 2022	Tegalpapak	1	3	8
14 January 2022	Tegalpapak	1	3	3
15 January 2022	Tegalpapak	1	1	1
10 January 2022	Lizui	1	4	2
11 January 2022	Lizui	1	7	5
12 January 2022	Lizui	1	8	8
13 January 2022	Lizui	1	5	6
30 January 2022	Frei Paulo	1	6	5
31 January 2022	Frei Paulo	1	2	1
01 February 2022	Frei Paulo	1	3	4
02 February 2022	Frei Paulo	1	5	3
05 February 2022	Gangarampur	1	3	7
06 February 2022	Gangarampur	1	1	2
07 February 2022	Gangarampur	1	7	3
08 February 2022	Gangarampur	1	9	9

This the second table which shows the Reporting date, which is the date on which the person reported his shift location.

Reporting Date	Name	Location
07 January 2022	Lizui	Palembang
14 January 2022	Tegalpapak	Sapporo
11 January 2022	Lizui	Vijayawada
31 January 2022	Frei Paulo	Johannesburg
07 February 2022	Gangarampur	Chongqing

The objective is to compare the Reporting date (from table 2) with the range of dates from the first table and if the Reporting date matches any of the dates, then the number of days in that date range is counted from table 1. The final results table is shown below. The table shows the name and the number of days spent per location per month, so if the shift period overlaps two months, then the number of days should be split between those two months, such as, for Frei Paulo.

Any help would be much appreciated!

Name	Year	Month	Number of days per location per month	Location
Lizui	2022	January	5	Palembang
Tegalpapak	2022	January	5	Sapporo
Lizui	2022	January	4	Vijayawada
Frei Paulo	2022	January	2	Johannesburg
Frei Paulo	2022	February	2	Johannesburg
Gangarampur	2022	February	4	Chongqing

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Get the min date and max date for table1 group by Name and continuous dates

2. Create a measure as below to get the number of days which reporting days between min date and max date

Number of days per location per month = 
VAR _year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR _month =
    SELECTEDVALUE ( 'Date'[Month] )
RETURN
    CALCULATE (
        SUM ( 'Work Shift'[Value] ),
        FILTER (
            'Work Shift',
            YEAR ( 'Work Shift'[Work Shift Date] ) = _year
                && 'Work Shift'[Work Shift Date].[Month] = _month
                && 'Work Shift'[Mindate] <= MIN ( 'Reporting'[Reporting Date] )
                && 'Work Shift'[Maxdate] >= MIN ( 'Reporting'[Reporting Date] )
        )
    )

Best Regards

[Greg_Deckler]

@Anonymous Maybe:

Number of Days Measure =
  VAR __Name = MAX('Table2'[Name])
  VAR __Date = MAX('Table2'[Reporting Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Table1',[Name] = __Name),
      "__Days",
          VAR __WorkDate = [Work Shift Date]
          VAR __Previous = MAXX(FILTER('Table1',[Name]=__Name) && [Work Shift Date] < __WorkDate)
        RETURN
          ([Work Shift Date] - __Previous) * 1.
  VAR __MinDate = MAXX(FILTER(__Table, [Work Shift Date] <= __Date && [__Days] <> 1),[Work Shift Date])
  VAR __MaxDate = MINX(FILTER(__Table, [Work Shift Date] <= __Date && [__Days] <> 1),[Work Shift Date])
RETURN
  (__MaxDate - __MinDate) * 1. + 1