Count items in a given datetime interval across several days

Question

&nbsp;I want a chart that shows number of agreements per day (no problem), but i want to be able to drill down to hours and still get the correct count.&nbsp;&nbsp;I have a Calendar table connected to the fact.&nbsp;I have a Time table with 1440 minutes/24hours, not connected to the fact.&nbsp;&nbsp;Fact tableI have generated one fact row per rented day per agreement ID to be able to connect the fact table to a calendar.&nbsp;&nbsp;| Agreement ID | Date From | Time From | Date To | Time To | &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01.03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;09:00 &nbsp; &nbsp; &nbsp; &nbsp; 05.03 &nbsp; &nbsp; &nbsp; 15:00 &nbsp; &nbsp; &nbsp; 01.03 (1row per date in interval)&nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02.03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12:00 &nbsp; &nbsp; &nbsp; &nbsp; 02.03 &nbsp; &nbsp; &nbsp; 15:00 &nbsp; &nbsp; &nbsp; &nbsp; etc..&nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05.03 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07:00 &nbsp; &nbsp; &nbsp; &nbsp;10.03 &nbsp; &nbsp; &nbsp; 16:00&nbsp;Base measure: &nbsp; &nbsp; &nbsp;Count: =distinctcount([Agreement ID]Then i have to somehow only count between the correct time intervall..&nbsp;&nbsp;Using a single calculate doesn't seem to be possible.. I think i need one for the start date and one for the end date.&nbsp;&nbsp;I tried controlling which Calculate to run by using an IF statement. One measure for the start date, one for the interval and one for the end date. This works fine as long as&nbsp;Agreement ID&nbsp;is present in the visualizaton, if its not visible the IF statement will iterate on the whole data set instead of on each unique agreement ID.&nbsp;Can i force the IF to check each row without having Agreement ID in the visualization?&nbsp;&nbsp;&nbsp;=if( max([Calender[Date])=min(Fact[DateFrom))&nbsp; &nbsp; &nbsp;then &nbsp;[Measure X]&nbsp; &nbsp;else if( max(Calender[Date]) = max(Fact[Date To]) ;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;then [Measure Y]&nbsp; &nbsp; &nbsp; &nbsp;else [Count]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;fenixen,I am still confusing your requirement, could you please share more details for further analysis?Based on my understanding,&nbsp;you should use the following formula to get the every day's active number of agreements.
Number of Agreement=CALCULAT(COUNTA(Fact[AgreementID]),FILTER(Fact,AND(Calendar[date]&lt;=MIN(Fact[Date to]),Calendar[date]&gt;=MAX(Fact[Date From]))))
In addition, what's your mean of [Measure X] and&nbsp;[Measure Y]?Best Regards,Angelia

v-huizhn-msft · Answer

Hi&nbsp;fenixen,I create three measures using the following.
Min = MIN('dimTime/Clock'[Hour Number])
Max = MAX('dimTime/Clock'[Next Hour])
#Measure = CALCULATE([#Agreements],filter(factReservations1rowperday,and(factReservations1rowperday[Time from]&lt;='dimTime/Clock'[Min],factReservations1rowperday[Time to]&gt;='dimTime/Clock'[Max])))
Then the #Measure as value level in Matrix preview, please see the screenshot.Best Regards,Angelia

fenixen · Answer

Hi&nbsp;v-huizhn-msft!&nbsp;&nbsp;Sorry for being unclear in my first post and thanks for the interest in my problem.&nbsp;Getting every day's active numnber of agreements is OK, but I want to account for the time of day. If the agreement ends at 15:00 it shouldn't be counted at 15:01. My current problem is that the agreements are counted for all 24 hours of the day.&nbsp;&nbsp;&nbsp;ExampleAgreement: &nbsp; Starts on the 10th of april at 10:00 and ends at the 12th of april 15:00&nbsp;The agreement should count from 10:00 the first day, then it should count for all minutes/hours of the second day (11.04) and stop counting at 15:00 the third day. &nbsp;This would be easy to solve if the agreements started and ended on the same or adjacent days, but an agreement could be active for 2 weeks for instance..&nbsp;&nbsp;If possible please have a look at the demo .pbix I've created to&nbsp;help understanding and hopefully solve the issue :)&nbsp;Dropbox download url&nbsp;&nbsp;

fenixen · Answer

Hi&nbsp;v-huizhn-msft&nbsp;Your suggestion is pretty close to the solution I got but its still not getting it correct. I've created a report page in the .pbix file which uses your three measures.&nbsp;&nbsp;Issue #1&nbsp;The agreements count from 00.00 each day, even if the from period starts at 12:00 that day.&nbsp;&nbsp;Issue #2&nbsp;The measures doesn't account for the fact that a renting period could last for several days. If an agreement is active for 5 days it should count for the 3 days in the middle and for active "hours" during first and last day.&nbsp;&nbsp;In the picture below we can se the agreements resetting each days since the measure only accounts for "hour", ignoring date from and date to.&nbsp;&nbsp;&nbsp;&nbsp;Updated demo-file:&nbsp;https://www.dropbox.com/s/oe5xvz9voamr0l5/Demo.pbix?dl=0

fenixen · Answer

Hi again&nbsp;v-huizhn-msft&nbsp;I've created a measure which returns the perfect answer given we provide the agreementID in the visualization.&nbsp;&nbsp;In the new .pbix file on the report named "New test 19.04" ive created both a matrix and graph that displays the correct number of agreements for each hour and day, seems perfect right?&nbsp;&nbsp;Problem is that you dont always want to display all the agreementID's in the visualization, the line graph looks TERRIBLE with all the ID's visible. When removing the agreementID the visualisations don't work anymore. Logical.. but i cant figure out how to fix it :(&nbsp;&nbsp;https://www.dropbox.com/s/oe5xvz9voamr0l5/Demo.pbix?dl=0&nbsp;&nbsp;The measures&nbsp;#Agreements works if AgreementID in graph = if(	min(factReservations1rowperday[DateFrom]) = [MinDateCalendar];
									[#Agreements Startdate];
	                 			if(	MAX(factReservations1rowperday[Date To]) = [MaxDateCalendar];
										[#Agreements End date];
										[#Agreements]
									)
								)Helping measures: #Agreements Startdate = CALCULATE([#Agreements];FILTER(factReservations1rowperday;factReservations1rowperday[Time from]&lt;='dimTime/Clock'[Maxtime]))#Agreements End date = CALCULATE([#Agreements];FILTER(factReservations1rowperday;factReservations1rowperday[Time to]&gt;'dimTime/Clock'[MinTime]))&nbsp;&nbsp;

Forum Discussion

Count items in a given datetime interval across several days

7 Replies

Related content

Slicer for filtering several columns

Converting Epoch Datetime into regular datetime format

Convert DateTime to Date

Time Interval Grouping

Change Y axis interval