Creating Business Date in Date Table

[djj13]

I have a report that is generated by our Point-of-Sale system. The report has the date/time a check is opened and when it is closed.

Our business day for this report runs from 3:00AM - 2:59 AM. I need to add a date column to represent the business/transaction day. 

Open Date/Time AFTER 1/1/2020 3:00:00 AM  Close Date/Time BEFORE 1/2/2020 2:59:00 AM =  Business Date of 1/1/2020.

Examples:

Open Date/Time          Close Date/Time               Business Date

1/1/2020 09:00:00 AM    1/1/2020 09:30:00 AM    1/1/2020

1/1/2020 11:00:00 PM    1/2/2020 01:30:00 AM    1/1/2020

1/2/2020 01:00:00 AM    1/2/2020 02:00:00 AM    1/1/2020

[amitchandak · Accepted solution]

@djj13 , Try a new column like

Business Date =
Switch( true(),
hour([Open Date/Time]) >= 3 , [Open Date/Time].date , // or use date(year([Open Date/Time]), month([Open Date/Time]), day([Open Date/Time])) ,
hour([Close Date/Time]) < 3 ,[Open Date/Time].date  -1,
[Open Date/Time].date
)

[djj13]

Thank you! This worked. I tried using [Open Date/Time].date but received a syntax error for the date function.

dj.

[mahoneypat]

You can use a column expression like this to do that.

Business Date 2 = var thisdate = DATE(YEAR('Table'[Open Date/Time]), MONTH('Table'[Open Date/Time]), DAY('Table'[Open Date/Time]))

return if(HOUR('Table'[Open Date/Time])<3, thisdate -1, thisdate)

Regards,

Pat