Forum Discussion
Working with date time functions gone wrong
Hi Anonymous
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.
-----------------------------------------------------
HI Anonymous
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.
-----------------------------------------------------
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.
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.
-----------------------------------------------------