Time interval between week days and hours

[Anonymous]

Hello. I have the following problem:

I have a file with a field called "PaymentDateHour" and it has date and hour format (09-12-2020 17:32:00).   I need to group my table' entries by cycles with the following logic:

Beginning	End	Cycle
Fri  11:00:00	Mon 10:59:59	30
Mon 11:00:00	Tue 10:59:59	40
Tue 11:00:00	Wen 10:59:59	50
Wed 11:00:00	Thu 10:59:59	60
Thu 11:00:00	Mon 10:59:59	20

I need help to convert the date and hour format to weekday and hour format and then place in the respective cycle.

If a order was pay between friday 11:00:00 and monday 10:59:59 the cycle is 30 and so on. 

Any ideas?

Thanks!

[AlB · Accepted solution]

Hi @Anonymous 

Cycle =
VAR cutOffT_ = 11 / 24
VAR shiftedDayTime_ = Table1[PaymentDayHour] - cutOffT_
VAR day_ =
    WEEKDAY ( shiftedDayTime_, 2 )
RETURN
    SWITCH ( day_, 1, 40, 2, 50, 3, 60, 4, 20, 30 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

Of course it does. See it at play in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

It didn't work: "An argument of function 'WEEKDAY' has the wrong data type or the result is too large or too small."

[AlB · Accepted solution]

Hi @Anonymous 

Cycle =
VAR cutOffT_ = 11 / 24
VAR shiftedDayTime_ = Table1[PaymentDayHour] - cutOffT_
VAR day_ =
    WEEKDAY ( shiftedDayTime_, 2 )
RETURN
    SWITCH ( day_, 1, 40, 2, 50, 3, 60, 4, 20, 30 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers