Forum Discussion

fenixen's avatar
fenixen
Icon for Advocate II rankAdvocate II
9 years ago

Count items in a given datetime interval across several days

  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.    I have a Calendar table connected to the fac...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi 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]<='dimTime/Clock'[Min],factReservations1rowperday[Time to]>='dimTime/Clock'[Max])))


Then the #Measure as value level in Matrix preview, please see the screenshot.



Best Regards,
Angelia


fenixen's avatar
fenixen
Icon for Advocate II rankAdvocate II
9 years ago

Hi v-huizhn-msft

 

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. 

 

Issue #1 

The agreements count from 00.00 each day, even if the from period starts at 12:00 that day. 

 

Issue #2 

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. 

 

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. 

 

 

 

Updated demo-file: https://www.dropbox.com/s/oe5xvz9voamr0l5/Demo.pbix?dl=0