Calculate total orders in timeframe

DAX DAX Calculations
1

Hello everyone!

Beacause I can’t find any video’s and content about TIME, I have the following question. I would like to calculate nr of total orders in a “day” with a timeframe of: 21.00 - 20.59 (24 hours). So the days have a overlapping character. The image underneath will explain my question further.

image
image790×305 6.63 KB

Here I have the pbix file. I hope some could help. Any help is welcome!

Timeframe total orders.pbix (126.1 KB)

Nick

2

Hello @NickvanDijk,

Have a look at Problem of the Week #10 - Dealing with time Periods. There are different approaches that can help you

Best regards,
Alex Badiu

3

I created a new disconnected Time Table

image

Then I added the following measures:

StartDateTime = MIN('Date'[Date]) + DIVIDE( MIN('Time Table'[Hour]), 24,0) + 21/24

EndDateTime = MAX('Date'[Date]) + DIVIDE( MIN('Time Table'[Hour]), 24,0) + 21/24

Total Orders = COUNT('Order Table'[Order Nr])

Total Orders 2 = 
    VAR _StartDateTime= [StartDateTime]
    VAR _EndDateTime = [EndDateTime]
    RETURN
    CALCULATE([Total Orders], 
    FILTER('Order Table', 
        'Order Table'[Date Time Order]>= _StartDateTime
        && 
        MIN('Order Table'[Date Time Order])< _EndDateTime))

I marked as Date Table your Date table

And I get to the requested result. As you can see in the image below I calculate the number of orders between 5/19/2021 after 21h & 5/20/21 before 21h

image
image998×696 110 KB

Best regards,
Alex

Timeframe total orders ABadiu.pbix (1015.2 KB)

4 Likes
4

Hi Nick,
I got one solution, might not be an ideal one:

1.First, create a column in the Orders table. As this is a fact table, it is not recommended when rows reach certain amounts.
Calibrated Order Date = IF(and(Orders[Order Date]=Orders[Order Date], Orders[Order Time]>=TIMEVALUE(“21:00”)), Orders[Order Date]+1,Orders[Order Date])

image
image703×341 21.7 KB

  1. Create an inactive relationship between Dates[Date] and Orders[Calibrated Order Date].
    image

  2. Change Total Orders formula to the following:
    Total Orders =
    CALCULATE(COUNTROWS(Orders), USERELATIONSHIP(Dates[Date],Orders[Calibrated Order Date]))

The pbix file is attached:
Timeframe total orders_Solution1.pbix (131.6 KB)

I hope it will help.
David Cui 2021-05-20T06:00:00Z

1 Like
5

Hello @NickvanDijk

Solution is attached for your quick reference.

image
image1863×486 48.1 KB

I hope this helps.

Regards,
Kumail RazaTimeframe total orders.pbix (137.3 KB)

6

Hi @NickvanDijk, did the response provided by @Kumail help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @NickvanDijk, we’ve noticed that no response has been received from you since the 21st of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hello guys!
Thanks for your help all three of you!
Awesome work
Best Regards,
Nick