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