Forum Discussion
A Slowly Changing Dimension
Hi,
I need some help with what I think is a slowly changing dimension. Let me try to explain.
The suppliers are given a time(window) at which they can deliver there goods. This time(window) has a start and end date.
Example: in 2023 delivery between 07:00 and 08:00, in 2024 delivery between 06:00 and 07:00
So I would like to compare their effective delivery time with their planned time. Of course taking their schedule into account.
There are main 3 tables : Supplier, Planned, Delivery :
Supplier is the Supplier dimension table. Planned has the planned schedule for each supplier and Delivery is my fact table.
| Supplier |
| S1 |
| S2 |
Planned
| Supplier | start date | end date | planned time begin | planned time end |
| S1 | 01/01/2023 | 31/12/2023 | 07:00 | 08:00 |
| S1 | 01/01/2024 | 31/12/2024 | 06:00 | 07:00 |
| S2 | 15/01/2023 | 14/01/2024 | 10:00 | 11:00 |
| S2 | 15/01/2024 | 14/01/2025 | 11:00 | 12:00 |
Delivery table
| date | supplier | ordered | delivery time |
| 01/01/2023 | S1 | 11 | 06:30 |
| 02/01/2023 | S1 | 21 | 07:30 |
| 03/01/2024 | S1 | 31 | 08:30 |
| 04/01/2024 | S1 | 41 | 05:30 |
| 05/01/2024 | S1 | 51 | 06:30 |
| 06/01/2024 | S1 | 61 | 07:30 |
This is the relationship schema.
So the outcome should look like this
| data | Supplier | ordered | delivery time | planned time begin | planned time end | result |
| 01/01/2023 | S1 | 11 | 06:30 | 07:00 | 08:00 | not on time |
| 02/01/2024 | S1 | 21 | 07:30 | 07:00 | 08:00 | on time |
| 03/01/2024 | S1 | 31 | 08:30 | 07:00 | 08:00 | not on time |
| 04/01/2024 | S1 | 41 | 05:30 | 06:00 | 07:00 | not on time |
| 05/01/2024 | S1 | 51 | 06:30 | 06:00 | 07:00 | on time |
| 06/01/2024 | S1 | 61 | 07:30 | 06:00 | 07:00 | not on time |
Both a dax and a power query solution are okay. No idea what would be better.
Hopefully someone can help or at least get me started
Ronny
Hi rodg
You can refer to the following solution, the sample data and the relationship is the same as you provided.
Start_date = MAXX(FILTER(Planned,YEAR([start date])=YEAR(MAX(D_Date[Date]))&&[start date]<=MAX(D_Date[Date])&&[end date]>MAX(D_Date[Date])),[start date])Begin_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time begin])Begin_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time begin])End_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time end])result = IF(MAX(F_Delivery[delivery time])>=[Begin_time]&&MAX(F_Delivery[delivery time])<[End_time]&&[Start_date]<>BLANK(),"on time",IF([Start_date]<>BLANK(),"not on time"))Then put the result measure, start time measure and end time measure to the visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi rodg
You can refer to the following solution, the sample data and the relationship is the same as you provided.
Start_date = MAXX(FILTER(Planned,YEAR([start date])=YEAR(MAX(D_Date[Date]))&&[start date]<=MAX(D_Date[Date])&&[end date]>MAX(D_Date[Date])),[start date])Begin_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time begin])Begin_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time begin])End_time = MAXX(FILTER(Planned,[start date]=[Start_date]&&[end date]=[End_date]),[planned time end])result = IF(MAX(F_Delivery[delivery time])>=[Begin_time]&&MAX(F_Delivery[delivery time])<[End_time]&&[Start_date]<>BLANK(),"on time",IF([Start_date]<>BLANK(),"not on time"))Then put the result measure, start time measure and end time measure to the visual.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.