Count unique days across multiple date ranges for a given criteria (DAX)
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
