Message: Hi I Need help in the data file Im working with. I cant figure out how I can calculate orders that completed before and after the set time(completebefore) and (completeafter).
the table below is my expected output. just need what DAX expression should I use to calculate the early orders and late orders.
if I were to put it in simple words if completiontime is < complete beforetime = early order if > complete after then late order.
here's the sample data
| shortId | Driver | completeBeforeTime | completeAfterTime | creationTime | startTime | completionTime |
| 250563 | Andy Abreu | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 15:37 | 1/10/2023 15:43 |
| 6219968 | Joseph Lokoyi | 12/20/2022 15:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 10:19 | 12/20/2022 10:21 |
| 10564100 | Joseph Lokoyi | 1/24/2023 18:00 | 1/24/2023 15:00 | 1/23/2023 21:00 | 1/24/2023 15:11 | 1/24/2023 15:36 |
| 11466434 | Samson Lawani | 12/21/2022 16:30 | 12/21/2022 11:00 | 12/21/2022 8:04 | 12/21/2022 18:47 | 12/21/2022 18:48 |
| 18317730 | Andy Abreu | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 15:06 | 1/10/2023 15:34 |
| 28653289 | Andy Abreu | 1/12/2023 18:00 | 1/12/2023 15:00 | 1/11/2023 21:00 | 1/12/2023 17:08 | 1/12/2023 17:09 |
| 45183630 | Andy Castilo | 1/5/2023 19:00 | 1/5/2023 16:00 | 1/4/2023 21:00 | 1/5/2023 18:03 | 1/5/2023 18:10 |
| 83912333 | Andy Castilo | 1/3/2023 16:00 | 1/3/2023 11:00 | 1/3/2023 6:57 | 1/3/2023 12:03 | 1/3/2023 12:26 |
| 89153174 | Andy Castilo | 1/3/2023 16:00 | 1/3/2023 11:00 | 1/3/2023 6:57 | 1/3/2023 11:03 | 1/3/2023 12:03 |
| 5.17E+37 | Joseph Lokoyi | 12/20/2022 15:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 10:21 | 12/20/2022 10:21 |
| 6.79E+82 | Ogu Osias | 12/20/2022 14:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 14:26 | 12/20/2022 14:45 |
| 3.90E+97 | Andy Castilo | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 17:09 | 1/10/2023 17:54 |
| 00719dec | Andy Abreu | 1/10/2023 19:00 | 1/10/2023 16:00 | 1/9/2023 21:00 | 1/10/2023 17:37 | 1/10/2023 17:37 |
| 008b9e85 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 19:29 | 1/8/2023 19:39 |
| 009b6ee1 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 18:46 | 1/8/2023 18:52 |
| 00dc7eb3 | David Mercado | 1/4/2023 16:00 | 1/4/2023 9:00 | 1/4/2023 7:23 | 1/4/2023 12:29 | 1/4/2023 13:13 |
| 010783c9 | Samson Lawani | 12/22/2022 19:00 | 12/22/2022 16:00 | 12/21/2022 21:00 | 12/22/2022 18:50 | 12/22/2022 19:23 |
| 01eedb2f | Ogu Osias | 12/22/2022 18:00 | 12/22/2022 11:00 | 12/22/2022 9:40 | 12/22/2022 11:13 | 12/22/2022 11:13 |
| 023464ca | Samson Lawani | 12/21/2022 16:30 | 12/21/2022 11:00 | 12/21/2022 8:04 | 12/21/2022 10:59 | 12/21/2022 10:59 |
| 0434c392 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 19:13 | 1/8/2023 19:13 |
| 0490866d | Andy Castilo | 1/24/2023 10:15 | 1/24/2023 7:00 | 1/23/2023 21:00 | 1/24/2023 8:57 | 1/24/2023 9:29 |
the table below is my expected output. just need what DAX expression should I use to calculate the early orders and late orders.
if I were to put it in simple words if completiontime is < complete beforetime = early order if > complete after then late order.
here's the sample data
| shortId | Driver | completeBeforeTime | completeAfterTime | creationTime | startTime | completionTime |
| 250563 | Andy Abreu | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 15:37 | 1/10/2023 15:43 |
| 6219968 | Joseph Lokoyi | 12/20/2022 15:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 10:19 | 12/20/2022 10:21 |
| 10564100 | Joseph Lokoyi | 1/24/2023 18:00 | 1/24/2023 15:00 | 1/23/2023 21:00 | 1/24/2023 15:11 | 1/24/2023 15:36 |
| 11466434 | Samson Lawani | 12/21/2022 16:30 | 12/21/2022 11:00 | 12/21/2022 8:04 | 12/21/2022 18:47 | 12/21/2022 18:48 |
| 18317730 | Andy Abreu | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 15:06 | 1/10/2023 15:34 |
| 28653289 | Andy Abreu | 1/12/2023 18:00 | 1/12/2023 15:00 | 1/11/2023 21:00 | 1/12/2023 17:08 | 1/12/2023 17:09 |
| 45183630 | Andy Castilo | 1/5/2023 19:00 | 1/5/2023 16:00 | 1/4/2023 21:00 | 1/5/2023 18:03 | 1/5/2023 18:10 |
| 83912333 | Andy Castilo | 1/3/2023 16:00 | 1/3/2023 11:00 | 1/3/2023 6:57 | 1/3/2023 12:03 | 1/3/2023 12:26 |
| 89153174 | Andy Castilo | 1/3/2023 16:00 | 1/3/2023 11:00 | 1/3/2023 6:57 | 1/3/2023 11:03 | 1/3/2023 12:03 |
| 5.17E+37 | Joseph Lokoyi | 12/20/2022 15:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 10:21 | 12/20/2022 10:21 |
| 6.79E+82 | Ogu Osias | 12/20/2022 14:30 | 12/20/2022 11:00 | 12/20/2022 7:33 | 12/20/2022 14:26 | 12/20/2022 14:45 |
| 3.90E+97 | Andy Castilo | 1/10/2023 18:00 | 1/10/2023 15:00 | 1/9/2023 21:00 | 1/10/2023 17:09 | 1/10/2023 17:54 |
| 00719dec | Andy Abreu | 1/10/2023 19:00 | 1/10/2023 16:00 | 1/9/2023 21:00 | 1/10/2023 17:37 | 1/10/2023 17:37 |
| 008b9e85 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 19:29 | 1/8/2023 19:39 |
| 009b6ee1 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 18:46 | 1/8/2023 18:52 |
| 00dc7eb3 | David Mercado | 1/4/2023 16:00 | 1/4/2023 9:00 | 1/4/2023 7:23 | 1/4/2023 12:29 | 1/4/2023 13:13 |
| 010783c9 | Samson Lawani | 12/22/2022 19:00 | 12/22/2022 16:00 | 12/21/2022 21:00 | 12/22/2022 18:50 | 12/22/2022 19:23 |
| 01eedb2f | Ogu Osias | 12/22/2022 18:00 | 12/22/2022 11:00 | 12/22/2022 9:40 | 12/22/2022 11:13 | 12/22/2022 11:13 |
| 023464ca | Samson Lawani | 12/21/2022 16:30 | 12/21/2022 11:00 | 12/21/2022 8:04 | 12/21/2022 10:59 | 12/21/2022 10:59 |
| 0434c392 | Andy Castilo | 1/8/2023 19:00 | 1/8/2023 16:00 | 1/7/2023 21:00 | 1/8/2023 19:13 | 1/8/2023 19:13 |
| 0490866d | Andy Castilo | 1/24/2023 10:15 | 1/24/2023 7:00 | 1/23/2023 21:00 | 1/24/2023 8:57 | 1/24/2023 9:29 |
@UlaDeil Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)
DAX is easy, CALCULATE makes DAX hard...
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
