Forum Discussion

wahlau's avatar
wahlau
Frequent Visitor
7 years ago
Solved

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...
Dashboards
Report Server
  • wahlau's avatar
    wahlau
    7 years ago

    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

     

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
7 years ago

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's avatar
wahlau
Frequent Visitor
7 years ago

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