Count Row with multi condition and multi cut off time

[bikgx]

Hi all;

i am a beginner but tried to figured out my issue by my self and search on this community but still confused,  i have dataset ( attached here),   https://docs.google.com/spreadsheets/d/1QCzmcZA4p8jHXXPBKLVbqpfgZGbU2mGO/edit?usp=sharing&ouid=10007... 

what i expected is; 

IF

CLIENTt ='AAA', Count picking state='waiting" where Date Created between TODAYDATE(-1) > HOUR 16:30 to TODAYDATE <= HOUR 16:30

IF

CLIENT = 'BBB ', Count picking state= 'waiting' where Date Created between TODAYDATE(-1) > HOUR 13:30 to TODAYDATE <= HOUR 13:30

ELSE 

Count picking state = 'waiting' where Date Created between TODAYDATE(-1) > HOUR 15.00 to TODAYDATE <= HOUR 15.00 

note IF TODAY DATE is 11/15/2022 and TODAYDATE(-1) is 11/14/2022 (YESTERDAY)

Result for CLIENT AAA = "6" waiting job

what makes me confused is condition count in  Interval Time (multi cut off time) , i tried to get Hour and Minute first and compared it, but it didnt worked

Thank you for your help;

Regards;

Azura

Client	Date Created	picking_state
AAA	11/15/2022 20:10	waiting
AAA	11/15/2022 11:09	waiting
AAA	11/15/2022 10:06	waiting
AAA	11/15/2022 15:16	waiting
AAA	11/14/2022 19:56	waiting
AAA	11/14/2022 15:11	waiting
AAA	11/14/2022 19:56	waiting
AAA	11/14/2022 18:11	waiting

[bikgx]

HI @Anonymous 

Thank you for your help,  i got your idea and get the result what i expected, 

danke

[Anonymous · Accepted solution]

@bikgx , please try the following measure:

WaitingJobCount = 
    SUMX(
        'Table1',
        VAR vTime = 
            SWITCH('Table1'[Client]
                ,"AAA", TIME(16, 30, 0)
                ,"BBB", TIME(13, 30, 0)
                ,TIME(15, 0, 0)
            )
        VAR vStartTime = (TODAY()-1) + vTime
        VAR vEndTime = TODAY() + vTime
        VAR vJobWaitingCount =
        IF(
          'Table1'[picking_state] = "waiting"
          && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime
          , 1
          , 0
        )
        RETURN vJobWaitingCount
    )

This give me these results below: