count the real number of requests

[mina97]

i have the following data 

date/time	type
19/9/2023 16:10:02	A
20/2/2023 16:10:02	B
20/2/2023 9:00:11	B
20/3/2023 12:22:34	A

i want the orders that comes after duty hours which is 7:00:00 to 15:00:00 to be included with the day after 

so the order number 2 should be summed with the orders on  20/3/2023 because it came on 16:10:02 which is after 15:00:00

please help and thank you 

[Dangar332]

Hi, @mina97

i am take these example

add column 

newtime = IF('Table'[time]>TIMEVALUE("15:00:00"),1,0)

and

newdate =

var currentdate = 'Table'[date]

var nextdate = MINX(FILTER('Table','Table'[date]>currentdate),'Table'[date])

var final = IF('Table'[newtime1]=1 &&  nextdate=BLANK() ,TODAY(),IF('Table'[newtime1]=1,nextdate,'Table'[date]))

return final

after adding two column it look llike these

put newdate and order to table 

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[ERD]

@mina97 , what's the desired output? Count orders per day? What do you do for the first order which is also outside your time range?

[mina97]

@ERD hi i appreciate your help

what's the desired output?Count orders per day? yes count the orders py day with a condition which " if order came between the time  7:00:00 to 15:00:00 count it as today's orders if not count it with tommorrows orders. i hope it is clear

What do you do for the first order which is also outside your time range? the time range starts from 1/june/2018 and ends at today's date no matter what date is it today i need it to be automatically linked with today.

THANK YOU FOR CONSIDERATION and please help

[ERD]

@mina97 , I'm still not sure about the expected output. Anyway, you can try this measure to achieve the result according to my understanding:

Measure =
VAR real_dates =
    ADDCOLUMNS (
        'Table',
        "@real_date",
            VAR current_time = [Time]
            VAR check_time =
                current_time >= TIME ( 07, 00, 00 ) && current_time <= TIME ( 15, 00, 00 )
            VAR current_date = [Date]
            VAR check_date =
                IF ( check_time, current_date, current_date + 1 )
            RETURN
                check_date
    )
VAR t =
    GENERATE (
        VALUES ( 'Date'[Date] ),
        VAR dt = 'Date'[Date]
        RETURN
            ROW ( "rows_amt", COUNTROWS ( FILTER ( real_dates, [@real_date] = dt ) ) )
    )
RETURN
    SUMX ( t, [rows_amt] )

Here I use a Date table, not connected to the data table:

Date in the first column is from the Date table.

If we compare with the original date: