How to add column base on time range for date

[wahlau]

Hi, I am beginer in PowerBI, also do search in forum that any post about time range found all is more about HR counting Hour function, here I have sales data from POS and our business hour is 8am to next day Morning 4am restaurant, I try lot of formula base on sales date to get the actual business date, one of the report is Sold item Beer sales vs Daily delivery Beer = Wastage of Beer. if I count as normal day sure in accurate.

Any function can help me solve this issue and How my dashboard always show yesterday sales.

Thanks 

[v-qiuyu-msft]

Hi @wahlau,

1. You can create a calculated column then change data type as datetime. 

StartTime = DATE ( YEAR ('Table2'[recdate] ), MONTH ( 'Table2'[recdate] ), DAY ( 'Table2'[recdate] ) )

& " 7:00:00"

2. Create another calculated column, change data type to date. 

WorkDate = IF('Table2'[recdate]<='Table2'[StartTime],FORMAT(Table2[recdate]-1,"Short Date"),FORMAT('Table2'[recdate],"Short Date"))

Best Regards,
Qiuyun Yu 

[wahlau · Accepted solution]

Hi @v-qiuyu-msft,

Thanks for reply and your solution not suitable for me, because my time range is from today 7am till tomorrow 6am = today date, however I have my own solution, let me share on it.

there will be 3 separate column for this, 

1.  Time = FORMAT(pos_retail_sales_dtls[recdate] ,"hh:mm:ss")

#Take out the time from rec_date.

2.  EndDay = IF (pos_retail_sales_dtls[Time] >= TIME ( 7, 0, 0 )&& pos_retail_sales_dtls[Time] < TIME ( 23, 59, 59 ),"0", IF (pos_retail_sales_dtls[Time] >= TIME ( 0, 0, 0 )&&pos_retail_sales_dtls[Time] < TIME ( 6, 0, 0 ),"-1"))

#Let filter timing 7am - 12 = 0 and 12 - 6am = 0-1 ; no matter what date is that, it should be 2nd value is -1 day

3. Date Select = if(pos_retail_sales_dtls[EndDay] = "-1" , pos_retail_sales_dtls[recdate]-1, pos_retail_sales_dtls[recdate])

#Final result example 1/1/2019 9am = 1/1/2019, 2/1/2019 1.20am = 1/1/2019