Select Latest ID From Slow Changing Dimension Table Based On Date Slicer

[alee5210]

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?

[RossEdwards]

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)

[alee5210]

Do you think that it is possible with just a single date table?

[RossEdwards]

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'.

[alee5210]

This is a bit of a pain as I've also added some additional fields to my date table so it looks like the below. 

Date		Reporting Week		Data Period
01/01/2024		01/01/2024
02/01/2024		01/01/2024
03/01/2024		01/01/2024
04/01/2024		01/01/2024
05/01/2024		01/01/2024
06/01/2024		01/01/2024
07/01/2024		01/01/2024
08/01/2024		08/01/2024
09/01/2024		08/01/2024
10/01/2024		08/01/2024
11/01/2024		08/01/2024
12/01/2024		08/01/2024
13/01/2024		08/01/2024
14/01/2024		08/01/2024
15/01/2024		15/01/2024
16/01/2024		15/01/2024
17/01/2024		15/01/2024
18/01/2024		15/01/2024		Last 2 Weeks
19/01/2024		15/01/2024		Last 2 Weeks
20/01/2024		15/01/2024		Last 2 Weeks
21/01/2024		15/01/2024		Last 2 Weeks
22/01/2024		22/01/2024		Last 2 Weeks
23/01/2024		22/01/2024		Last 2 Weeks
24/01/2024		22/01/2024		Last 2 Weeks
25/01/2024		22/01/2024		Last 2 Weeks
26/01/2024		22/01/2024		Last 2 Weeks
27/01/2024		22/01/2024		Last 2 Weeks
28/01/2024		22/01/2024		Last 2 Weeks
29/01/2024		29/01/2024		Last 2 Weeks
30/01/2024		29/01/2024		Last 2 Weeks
31/01/2024		29/01/2024		Last 2 Weeks

I use the reporting week and the data period field in my dashboard. I noticed that when I use the 2 tables together, the dashboard begins to function in an unusual way and does not give predictable reults.

If I try your method of the disconnected table it is working well.

But the moment I bring in the first connected table, things get a bit weird. I'm no longer getting the same results as I would have expected. Is there anyway that I can ammend this? This is driving me crazy!

[v-yanimei-msft · Accepted solution]

Hi all , 

@RossEdwards , 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.