Count unique days across multiple date ranges for a given criteria (DAX)

[Ska110]

Hello,

Another newbie, any help very much appreciated.

I would like to count the unique days in a given month for one (or more), given criteria using DAX.

E.g., for a particular Person id, I would like to calculate the actual number of days that a person received a service in a given month, whilst excluding overlapping days and gaps in periods, i.e., returning the actual 'utilisation' days in each of the month columns. 

So, for the data below, the total number of days in April person 101 is 33, but with overlapping and gap exclusions the actual utilisation is 27 days and listed in April’s column. Very many thanks.

Person id	Start date	End date	number of days	First	Apr-22	May-22	Jun-22
101	01/04/2022	20/04/2022	20	1	27	0	0
101	22/04/2022	28/04/2022	7	0	27	0	0
101	23/04/2022	28/04/2022	6	0	27	0	0
202	01/05/2022	03/05/2022	3	1	0	10	0
202	25/05/2022	31/05/2022	7	0	0	10	0
303	01/04/2022	05/04/2022	5	1	5	0	0
303	01/04/2022	05/04/2022	5	0	5	0	0
404	01/06/2022	07/06/2022	7	1	15	0	8
404	01/04/2022	15/04/2022	15	0	15	0	8
404	28/06/2022	28/06/2022	1	0	15	0	8

[Ska110]

Many Thanks, this helped great. Sorry for the delayed response. 

[Ska110]

Many thanks for your swift response! Just one issue the column named 'First' should have a 1 in the first row of any new person id. In your model, person id 404 its the second row that returns the 1, i think because the date range order? So first should just be based on the person id column denoting the first row of any person id.  

[Jihwan_Kim]

HI,

I tried to copy the data from the post. But I missed to compare it with the result that you posted. I think you posted something like below.

[Jihwan_Kim · Accepted solution]

Hi, please check the below picture and the attached pbix file.

expected result measure: =
VAR _currentrowID =
    SELECTEDVALUE ( Data[Person id] )
VAR _currentyearmonth =
    SELECTEDVALUE ( 'Calendar'[Year-Month sort] )
VAR _t =
    SUMMARIZE (
        GENERATE (
            FILTER ( ALLSELECTED ( Data ), Data[Person id] = _currentrowID ),
            FILTER (
                'Calendar',
                'Calendar'[Date] >= Data[Start date]
                    && 'Calendar'[Date] <= Data[End date]
            )
        ),
        'Calendar'[Date],
        'Calendar'[Year-Month sort]
    )
RETURN
    COUNTROWS ( FILTER ( _t, 'Calendar'[Year-Month sort] = _currentyearmonth ) )