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 am still confusing your requirement, could you please share more details for further analysis?

Based on my understanding, 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]<=MIN(Fact[Date to]),Calendar[date]>=MAX(Fact[Date From]))))


In addition, what's your mean of [Measure X] and [Measure Y]?

Best Regards,
Angelia

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

    Hi v-huizhn-msft! 

     

    Sorry for being unclear in my first post and thanks for the interest in my problem. 

    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. 

     

     

    ExampleAgreement:   Starts on the 10th of april at 10:00 and ends at the 12th of april 15:00

     

    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.  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.. 

     

    If possible please have a look at the demo .pbix I've created to help understanding and hopefully solve the issue :) 

    Dropbox download url

     

     

    • 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