Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
wahlau
Frequent Visitor

How to add column base on time range for date

2018-12-30_18-16-16.pngHi, 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 

 

1 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

 2019-01-05_19-07-49.png

 

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

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"
 
q1.PNG
 
 
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"))
 
q2.PNG
 
Best Regards,
Qiuyun Yu 
Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 2019-01-05_19-07-49.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.