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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Felipe_hcosta
New Member

Working with date time functions gone wrong

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

1 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:

Mikelytics_0-1669839079355.png

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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

5 REPLIES 5
Mikelytics
Resident Rockstar
Resident Rockstar

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @Mikelytics, thanks for replying! 

 

As requested: 

 

Felipe_hcosta_0-1669837785265.png

 

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 🙂

Mikelytics_0-1669837976252.png

But ok, lets say you have one table.

 

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

Mikelytics_1-1669838030026.png

Then please create a calculated column 

Mikelytics_2-1669838070142.png

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

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@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

Felipe_hcosta_0-1669838659448.png

 

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

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:

Mikelytics_0-1669839079355.png

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

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.