Working with date time functions gone wrong

[Felipe_hcosta]

Hi there

I'm kinda newbie at power bi and i'm trying to calculate using an if functions as below: 

Used formula: 
If('Dispatch'[Realized Dispatch date/time] < 'Receipt'[Dispach Deadline date/time], "on time", "out of time")

Examples: 

Realized Dispatch date/time
26/11/2022 06:54 

Dispatch Deadline

26/11/2022 07:54

The result must be true, but the BI is seeking for hour

[Mikelytics]

Hi @Felipe_hcosta 

I see in your formula that you arre referring to different tables. So as you wrote the function Power BI is not able to match line-items is my first guess. Can you please show a picture of the data model with tables an relations?

Do you use a calculated column or a measure?

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Felipe_hcosta]

Hi @Mikelytics, thanks for replying! 

As requested: 

[Mikelytics]

HI @Felipe_hcosta 

Thakn you, TO be honest this is a little bit confusing since you refer in your initial request to two tables and now you show me only one 🙂

But ok, lets say you have one table.

In the table view in Power BI Destop please select your table

Then please create a calculated column 

There please put in the following formula:

Column Name = 

If([Real Dispatch date/time] < [Dispach Deadline date/time], "on time", "out of time")

If this does not work please show me in PBI Desktop where a problem arises.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Felipe_hcosta]

@Mikelytics 

They are actually from different tables, i'm sorry for not been so clear and I wasn't printing because my data is in a different language. 

Let's go to the translated facts: 

Conection model

The photo I've sent before all the date was united into a table. 

[Mikelytics · Accepted solution]

ok,

so let me guess: in both tables you have a column with the order number right?

so first you need to do is to create based on the order columns a new table with all distinct order numbers. In best case you do it in Power Query. This new table you can call "Orders" or "Dim_Orders".

The you adjust your data model like this:

This is a typical star schema logic. If you are not aware of it please check the following ressource:

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

It is one of THE best practices to build star schema models and you should directly start from the beginning to apply these practices.

When you have the data model you can put the orders from the order table into a table visual. Afterwards you can create a measure like this:

Status =

If(MAX('Dispatch'[Realized Dispatch date/time]) < MAX('Receipt'[Dispach Deadline date/time]), "on time", "out of time")

When you add this measure into the table visual it should work. 

Please be aware that based on table granularity there might be some adjustments to be done but based on given input this is how I would start.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn