Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @wahlau,
1. You can create a calculated column then change data type as datetime.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |