Slowly changing dimension?

[Melissa]

Hi @rodwhiteley,

Sure. There’s been a lot of demand for this technique over the last week or so…

One thing to note. How to deal with dates after the last measurement? I would suggest retrieving the last know value from the result table in DAX but hey… Just review it and if at any point you consider changing that OR switching to another granularity because now it’s at the at the daily level, you can modify the applied logic accordingly…

1. So I started of with a small sample.
2. Manually added a Buffer step to load the table into memory
   
3. Created a nested table that contains only records where the [Test Date] from the inner table is greater than or equal to the [Test Date] from the outer table AND where the MRN match.
   Sorted that intermediate table and only kept the First 2 rows.
   
   
4. Determined the number of Days between the 2 [Test Date] values by checking if the nested table has 2 rows and using the access operator to subtracting the first- from the second date.
   
   
5. Calculated a Weighted step for each day by subtracting the first- from the second weight and dividing that by the Date difference between the two measurements.
   
   
6. Created a list of dates to fill the gab
   
   
7. Expanded those Dates to New Rows
   
   
8. Added a Weight projection by summing the [Weight] with the Weight step times Date difference
   
   
9. Cleaned up the helper columns.
   
   

.
Think we could consider it a fact table now… You have a Weight value for each Day and can create a relationship between the DateKey field and your Date dimension table.

I hope this is helpful. Here’s my sample file.
eDNA - SCD Body Weight projection over date range.pbix (45.6 KB)