Returning success or failure based on date/time comparison

[Jogchum]

Hey everyone, 

I'm pretty new at powerBI so I hope I can get some direction here. 

I work for an online store where the promise to the customer is that if they place an order before 18:00 it will be shipped the same day for next day delivery. I want to display per date wether that promise was met or not (if met: "Success" if not: "Failure"). I was thinking of displaying that data in a calendar visual. I just can't really get it to work.

I have a datetable which has a relation to the table with the date and time of the moment an order was packed. I use the datetable to populate the calendar to display only the packed orders. I use this to show how many orders were packed and shipped each day. See below for example. 

I then have two other tables, one has the date/time of the placement of the order (in a single column, formatted like: "*14-3-2001 13:30:55"). The other has the date of the order being packed. In order to know if the customer promise was met I need to evaluate for each day if the order date/time of the final packed order of that day is greater than the packing date/time of 18:00 hours. If greater, then success, if not failure. 

My idea was to use the date table to populate the calendar visual and then use the measure which returns "success" or "failure" as the Data. Any ideas on how to do this? 

[v-deddai1-msft]

Hi @Jogchum ,

Would you please inform us more detailed information( your  data (pbix file by OneDrive for Business)) if possible? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[Jogchum]

Hi @v-deddai1-msft , 

Sorry for taking a while to get back to you and thanks for trying to help out. It seems I'm not allowed to share with external users from onedrive so I here's a link from Google Drive: https://drive.google.com/file/d/16DSwoj675aAx8VopmeWzlWfIo4j7EQ_N/view?usp=sharing

The 'datum' column is from the datetable and provides the context. 

The 'Ordernumber' column is from the 'Picked and packed orderlines' table. It counts upward with every newly placed order so the highest number is the most recent order. 

The idea is that I only want to return 'success' or 'failure' for each unique date of the date table based on wether: 

The last packed ordernumber of that day (highest ordernumber) was placed on that same day after 18:00 pm. If that's the case then success, if not, failure. 

I hope this helps. Thanks again. 

Jogchum 

[v-deddai1-msft]

Hi @Jogchum ,

I'm confused about what is your order placed date column and what is your order packed date column in your sample pbix file. Would you please give us more details by  giving an example？

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Jogchum]

Hi @v-deddai1-msft ,

I now recognize some of what I did wrong. I added an actual date table this time! I then created a relationship to 'Total hours per time of day' which in turn has a relationship with 'Packed orders' (which contains the date/time of when packed). I think this allows me to filter by date, but also lets me see where we were at at each hour or the work day. 

As for identification of the respective columns, see image below. 

On the 27th of august we failed to meet our customer promise because the last packed order was placed on the same day at 17: 50:40. So the result for the 27th of august should be 'failed'. 

Link to updated version: https://drive.google.com/file/d/1WajYYQwk6bn5nuyeCesIaSXAbKDK3req/view?usp=sharing

Best, 

Jogchum 

[v-deddai1-msft]

Hi @Jogchum ,

Can't get your shared file, please check the permission.

Best Regards,

Dedmon Dai

permissionpermission

[Jogchum]

Hi @v-deddai1-msft 

Apologies, should work now. 

https://drive.google.com/file/d/1WajYYQwk6bn5nuyeCesIaSXAbKDK3req/view?usp=sharing

[v-deddai1-msft]

Hi @Jogchum ,

I have spend a lot of time trying to  understand your requirements and data models. But  I'm really confused,   you say that last packed order was placed on the same day at 17: 50:40. So the result for the 27th of august should be 'failed'.  so which column show the time that order was placed?

Best Regards,

Dedmon Dai

[Jogchum]

Hi @v-deddai1-msft ,

I realise you're spending a lot of time on my project and I am super thankful! I will try and clarify a few things. The screenshot below shows you what I mean when I say that the final packed order of 27th of august was placed at 17:50:40. 

I see that you're confused with the difference between the column 'Allocatietijd' and 'Ordertime'. The colum 'Allocatietijd' shows you when an order was allocated (we have more than one shipping warehouse so an order gets allocated by our ordermanagementsoftware after a customer places the order). The allocation time plays no role in our customer promise, so it is not relevant. We're only looking at 'OrderTime'. It is also the column which is being shown in the screenshot above. 

Hope this clears things up. 

Thanks again, 

Jogchum 

[v-deddai1-msft · Accepted solution]

Hi @Jogchum ,

If I understand you correctly, you can use the following measure:

customer promise = VAR a = MAX('Picked and packed orderlines'[Ordernumber]) return IF(CALCULATE(MAX('Picked and packed orderlines'[OrderTime]),'Picked and packed orderlines'[Ordernumber] = a)>TIME(18,0,0),"Success","Failed")

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Jogchum]

Hey @v-deddai1-msft , 

Yes, this works! Thanks so much! I changed the formula and added a few additional nested if-statements to check for some additional situations (if it's a weekend, if we're behind schedule for more than a day, if there's no data on a day). I think it all works. 

The calendar visual I am using doesn't display any text (apparently none of them do), so I changed the output of the if-statement to 0 (failed) and 1 (success). 

Thanks again for all your effort, I learned a lot. 

Jogchum