Forum Discussion

stlk's avatar
stlk
Frequent Visitor
4 years ago

Count days between dates if specific column is > 0

Hi, I need some help 

I have a query table "AllUnits" with a the columns

"ALL.ID"  "User", "Start", "End", "Allocation"

 

In another query table "Available hours" i have columns
"Date", "Ava.Hours" , "User2"

 

 

Every "Date" have a value in "Available hours" of 8 if it is not a Weekend or Holiday. For every Employee there is the same set of day. so Many Dates (sorry there is a type fault a sunday with value of 😎 

 

And i Have and overall "Date table" Table 

 

 

The result is to count days between "StartsAt" and "EndsAt" if the date between start and end has a value in "Ava.hours" > 0 

Have a meassure:

Measure = CALCULATE(
COUNT('Avaiable hours'[Ava.Hours]),
DATESBETWEEN('Date table'[Date], MAX( Allocation[start] ), MAX(Allocation[end])), ALL('Date table'),'Avaiable hours'[Ava.Hours] > 0
)
 
My Filters in my Calculate doesnt work, Can you help with that. It doesnt seem like it looks in the dates between
Right now it Counts All of them and the result is 11 for all

 

But it should be 5, 4 and 5
Can you help me filter the rigth answer or what is wrong with my meassure
Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    "OBS: Some start and end dates can overlap"

     

    Please explain what to do in such a scenario.

    • stlk's avatar
      stlk
      Frequent Visitor
      4 years ago

      It was only an information with regards to how to identify them seperately

      So a person can be allocated to Project 1 for 3 hours and Project 2 for 7 hours the same day, 

      In the end if i sum it up he will be booked for 10 hours in total on one day. 

       

      My main problem is after getting a list of days between the to dates how to destingues between workday or of day that is based on the other table if the value on that specific date is larger than 0. 

       

      I have managed to split this out to a list of date between the dates for each ALL.ID.No

       

       

      So i need to count for each serie/list of date for a project All.ID:No the number of dates with a value higher than 0 in allocation. and write in a new column 

      When i have managed that i think i am good to go from there

       

      Hope this helps a little

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        "

        So a person can be allocated to Project 1 for 3 hours and Project 2 for 7 hours the same day, 

        In the end if i sum it up he will be booked for 10 hours in total on one day. "

         

        Still not clear.

         

        Let's say they are assigned to project A from 9 am to 12 pm and to project B from 10am to 5pm.  How many hours total?