Count dates outside of time intervals

[MLC]

Hello, 

I have a dataset that looks like this (dates are in dd/mm/yyyy): 

	Start	End
A	01/01/2020	08/01/2020
B	06/01/2020	10/01/2020
C	15/01/2020	31/01/2020

I want to know how many days are not covered in these intervals, in a certain time period. So for example, in January 2020, it should return 4 (i.e. 11/01, 12/01, 13/01, 14/01). If I expand the time period to January + February 2020, it should return 33 (= 4 days in jan + 29 days in feb). 

I can calculate how many days are covered by the time intervals, but since they overlap (eg 'B' overlaps with 'A'), I can't use this number to substract from the entire time period. 

Any help on this would be appreciated!

[az38]

Hi @MLC 

first, you need to create calendar table. like

CalendarTable = CALENDAR(MIN('Table'[Start]);MAX('Table'[End]))

or

CalendarTable = CALENDARAUTO()

next, add a column to your CalendarTable

Column = CALCULATE(COUNTROWS('Table'); FILTER(ALL('Table'); 'CalendarTable'[Date]>='Table'[Start] && CalendarTable[Date] <= 'Table'[End]))

then filter out all Column <1

or create one big table and then group it by monthes, years, etc

CalendarTable = FILTER(
ADDCOLUMNS(
CALENDAR(MIN('Table'[Start]);MAX('Table'[End]));
"Count";CALCULATE(COUNTROWS('Table'); FILTER(ALL('Table'); [Date]>='Table'[Start] && [Date] <= 'Table'[End]))
); [Count] <1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[MLC]

Hey @az38 , 

Thanks a lot! I tried both proposed set-ups and they work, but I can't get the filtering to work. For example, when I want to filter out 'B', it doesn't do it... It keeps taking all entries in the database into account.. Any idea how to fix this? 

I also have the following measure to count the days in the intervals (the 'planning' table is the one with the intervals): 

Counting_days =

VAR START_DATE = MIN ( 'Calendar'[Date])

VAR END_DATE = MAX ( 'Calendar'[Date] )

RETURN

SUMX ( SUMMARIZE (

Planning,

Planning[Start],

Planning[End],

"DAYS", IF (Planning[Start] < START_DATE

&& Planning[End] < START_DATE

|| Planning[Start] > END_DATE

&& Planning[End] > END_DATE,

"0",

DATEDIFF(IF ( Planning[Start] < START_DATE, START_DATE, Planning[Start] ),

IF ( Planning[End] > END_DATE, END_DATE, Planning[End] ),

DAY

))), [DAYS])

Adjusting this measure to only count 'unique' days, would probably also do the trick (so only counting each day once, even if they are in 2 intervals). Then I could substract this from the total time period..  

Thanks again! 

MLC

[az38]

@MLC 

create a measure in your calendar table

Measure = 
IF(ISFILTERED('Table'[PeriodName]);CALCULATE(COUNTROWS('Table'); FILTER(ALL('Table'); SELECTEDVALUE('CalendarTable'[Date])>='Table'[Start] && SELECTEDVALUE(CalendarTable[Date]) <= 'Table'[End] && 'Table'[PeriodName]=SELECTEDVALUE('Table'[PeriodName])));
CALCULATE(COUNTROWS('Table'); FILTER(ALL('Table'); SELECTEDVALUE('CalendarTable'[Date])>='Table'[Start] && SELECTEDVALUE(CalendarTable[Date]) <= 'Table'[End])))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[MLC]

@az38 

I created the measure and I can make a table in the report that lists all applicable dates of the time intervals. But I can't get it to calculate the total amount of all dates (so either a row count or a sum).. 

Another approach could be to convert the time intervals to a dates-table (column A is 'date', column B is 'time interval ID'): 

...

07/01/2020 - A

07/01/2020 - B

08/01/2020 - A

...

But this already adds up to over 8000 rows... 

Thanks again! 

[az38]

@MLC
OK. Let’s go from the other side 🙂
How should look like your desired output? Could you give an example?

[MLC]

@az38 

I would like it to return a number. So if I select 'january 2020', it give me '4'. If I select jan+feb 2020, it returns '33'.. So the amount of days that are not part of any (applicable) interval. 

So I want to show that number and also how much that number is of the total time chosen period (%). But that last one should be easy (hopefully 😉 ) once I get the measure to work... 

Hope this makes it more clear? 

[az38]

@MLC
Measure works, I have checked 🙂 But you need to remove filter outside the calculated table statement before.
Should it be the only digits in card visual or a column in table visual?

[MLC]

@az38 

I've created a pbix file with just the dummy data (the data I mentioned in the first post), but I really can't get your measure to work... It only shows me '1' or 'empty' in a card visual, no matter what I do. So I have 2 tables: 1 with period name / start / end date and 1 calculated calendar table. The measure is in the calendar table.. I must be doing something wrong, but I don't see what it is... 

[az38 · Accepted solution]

Hi @MLC 

Now, its clear... I hope so 🙂

try this

1. create a calendar table

CalendarTable = 
CROSSJOIN(CALENDAR(MIN('Table'[Start]);MAX('Table'[End]));DISTINCT('Table'[PeriodName]))

2. Add to calendar table 2 calculated columns

Count = CALCULATE(COUNTROWS('Table');FILTER(ALL('Table');'Table'[Start]<=[Date] && 'Table'[End] >= [Date] && 'Table'[PeriodName]=CalendarTable[PeriodName]))

and

CountByDay = calculate(SUM(CalendarTable[Count]);ALLEXCEPT(CalendarTable;CalendarTable[Date]))

3. Add a final Measure

Measure = IF(ISFILTERED(CalendarTable[PeriodName]);
CALCULATE(DISTINCTCOUNT(CalendarTable[Date]);CalendarTable[Count]<1);
CALCULATE(DISTINCTCOUNT(CalendarTable[Date]);CalendarTable[CountByDay]<1))

pbix-file is here https://ufile.io/aqyv7xht 

do not hesitate to give a kudo to useful posts and mark solutions as solution

[MLC]

@az38 

Yes, this works! Thanks!!