Calculate between dates and times

[Anonymous]

Hello,

I'm looking for some help with determining the number of orders between two dates and times and hope I can get some guidance here. I am able to calculate if yesterday and today with: 

OrdersPending = CALCULATE ( 
DISTINCTCOUNT ( order[Delivery] )
 , DATESBETWEEN ( order[Order Date]
 , TODAY()-1
 , TODAY())
 , order[location_check] = 0
 )

However I'm not clear on how it would work with the date/time portion of between yesterday at 2pm and today at 2pm. 

Many thanks for help and clarification.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Put Today and Today-1 in the Card, Today shows 12:00:00AM of the current day, Today-1 shows 12:00:00AM of yesterday

So the range of Today and Today-1 is >= 12:00:00AM of yesterday &&<= 12:00:00AM of today

If you want to calculate yesterday's 2:00 pm to today's 2:00 pm

The following dax can be used:

OrdersPending1 =
CALCULATE(
    DISTINCTCOUNT('Table'[order]),
    FILTER(ALL('Table'),'Table'[date]>=TODAY() - 10/24 &&'Table'[date]<=TODAY() + 14/24 &&'Table'[location_check]=0))

If you want to calculate yesterday's 12 am to today's 12 pm

The following dax can be used:

OrdersPending2 =
CALCULATE(
    DISTINCTCOUNT('Table'[order]),
    FILTER(ALL('Table'),'Table'[date]>=TODAY()-1 &&'Table'[date]<TODAY() +1 &&'Table'[location_check]=0))

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

First of all you would have to specify which 2PM you mean.  And then you would have to replace the DATESBETWEEN with a custom formula that does number math  (2PM would be 14/24 = 0.58333333) .