Forum Discussion
Helper IISelect Latest ID From Slow Changing Dimension Table Based On Date Slicer
Hi everyone
I am looking to grab the latest data for each ID based on a dropdown slicer that the user selects. I have 2 tables, one is a slow changing dimension table (I think this is what it is called) and the other is a date table. I have to connect them so there is a relationship between the Data[Effective Start Date] and DateTable[Date]. I don't know if these columsn are the correct ones to form the relationship but this is what I have done.
To explain how the data works, we have a test ID and the date submitted never changes. But our users might update the mass if they remeasure it and find it to be different. So for Test ID = 1111, the mass was measured as 100 on 1 Jan, 2024 (effective start date) but after remeasurement on the 14th Jan, 2024 (effective end date) there was an error and it was resubmitted as 105 on the 15 Jan, 2024 (the new effective start date). For the latest record for reach Test ID, the effective end date is just put as 31 Dec, 2099 by default.
Data table:
| Test ID | Date Submitted | Mass | Effective Start Date | Effective End Date | ||||
| 1111 | 01-Jan-24 | 100 | 01-Jan-24 | 14-Jan-24 | ||||
| 1111 | 01-Jan-24 | 105 | 15-Jan-24 | 31-Dec-99 | ||||
| 2222 | 15-Jan-24 | 35 | 15-Jan-24 | 22-Jan-24 | ||||
| 2222 | 15-Jan-24 | 37 | 23-Jan-24 | 31-Dec-99 | ||||
| 3333 | 01-Jan-24 | 10 | 01-Jan-24 | 14-Jan-24 | ||||
| 3333 | 01-Jan-24 | 100 | 15-Jan-24 | 22-Jan-24 | ||||
| 3333 | 01-Jan-24 | 1000 | 23-Jan-24 | 31-Dec-99 | ||||
| 4444 | 15-Jan-24 | 50 | 15-Jan-24 | 31-Dec-99 | ||||
| 5555 | 29-Jan-24 | 70 | 29-Jan-24 | 31-Dec-99 | ||||
| 6666 | 01-Jan-24 | 18 | 01-Jan-24 | 31-Dec-99 |
Date table:
| Date |
| 01/01/2024 |
| 02/01/2024 |
| 03/01/2024 |
| ... |
| 31/01/2024 |
I want users to be able to use a dropdown from the date table, and from there it will grab the latest data from before the date selected.
E.g. If i select the user selects 2 Jan, 2024
| Test ID | Date Submitted | Mass | Effective Start Date | Effective End Date | ||||
| 1111 | 01-Jan-24 | 100 | 01-Jan-24 | 14-Jan-24 | ||||
| 3333 | 01-Jan-24 | 10 | 01-Jan-24 | 14-Jan-24 | ||||
| 6666 | 01-Jan-24 | 18 | 01-Jan-24 | 31-Dec-99 |
If they select 16 Jan, 2024
| Test ID | Date Submitted | Mass | Effective Start Date | Effective End Date | ||||
| 1111 | 01-Jan-24 | 105 | 15-Jan-24 | 31-Dec-99 | ||||
| 2222 | 15-Jan-24 | 35 | 15-Jan-24 | 22-Jan-24 | ||||
| 3333 | 01-Jan-24 | 100 | 15-Jan-24 | 22-Jan-24 | ||||
| 4444 | 15-Jan-24 | 50 | 15-Jan-24 | 31-Dec-99 | ||||
| 6666 | 01-Jan-24 | 18 | 01-Jan-24 | 31-Dec-99 |
If they select 31 Jan, 2024
| Test ID | Date Submitted | Mass | Effective Start Date | Effective End Date | ||||
| 1111 | 01-Jan-24 | 105 | 15-Jan-24 | 31-Dec-99 | ||||
| 2222 | 15-Jan-24 | 37 | 23-Jan-24 | 31-Dec-99 | ||||
| 3333 | 01-Jan-24 | 1000 | 23-Jan-24 | 31-Dec-99 | ||||
| 4444 | 15-Jan-24 | 50 | 15-Jan-24 | 31-Dec-99 | ||||
| 5555 | 29-Jan-24 | 70 | 29-Jan-24 | 31-Dec-99 | ||||
| 6666 | 01-Jan-24 | 18 | 01-Jan-24 | 31-Dec-99 |
Does anyone know how this could be done?
Hi all ,
Anonymous , thanks for your concern about this case, I have some additions for problem solving.
alee5210 , thank you for giving a specific description of the problem. Based on your imformation, I will give you the following steps to solve the problem.
1.The test table Data table, Date table, EffectiveDate.
2.The test model.
3.New Measure FILTER, and input:
FILTER = VAR _select = SELECTEDVALUE(EffectiveDate[Date]) VAR _End = CALCULATE( MAX('Data table'[Effective End Date]), ALLEXCEPT('Data table', 'Data table'[Test ID]), 'Data table'[Effective Start Date] <= _select ) RETURN IF( MAX('Data table'[Effective Start Date]) <= _select && MAX('Data table'[Effective End Date]) = _End, 1, 0 )4.Add Date to Field.
5.Add FILTER to Filters on this visual.
6.The outcome is in the following pictures.
If they select the user selects 2 Jan, 2024
If they select 16 Jan, 2024
If they select 31 Jan, 2024
All above contents can answer your original questions. For your latest questions, it probably due to the relationship you have between the Data table and the Date table. When the relationship looks like this picture, the same result will appear as you.
Best Regards,
Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
The solution i'd use is to have 2 different Calendar tables. The main table is the one that acts as your model's calendar table and is connected to your fact table using the "Date Submitted" field.
The 2nd calendar table would be purely a disconnected table used to select "As At", to allow your users to change where in time they want a result for.
From here you could get the a sum of all the right masses by doing:
Base Measure:Mass Sum = SUM('Data Table'[Mass])Date Constrainted output:
Current Mass = var selectedDate = MAX('Date Selection'[Date]) var output = CALCULATE( [Mass Sum], 'Data Table'[Effective Start Date] <= selectedDate, 'Data Table'[Effective End Date] >= selectedDate, ) RETURN output(note, i've used MAX rather than SELECTEDVALUE to increase the robustness of where this could be used)
alee5210Do you think that it is possible with just a single date table?
No. Your normal date table needs to be configured to handle the filter context of how dates relate to your data, in the manner as if you didn't store the history. The 2nd date table is required to handle the filter context on what is considered "now".
The reason is because you have 2 dimensions of data and overlaying a 3rd separate dimension, which is your 'where in the 2 dimensions do you want to see'.
