Forum Discussion
How to add column base on time range for date
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
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
2 Replies
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 YuHi 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
