Slowly changing dimensions current status vs history

[Janou]

Hi,

I have been struggling with a way to visualize data in the present and in the past while using slowly changing dimensions. 

The data looks like this:

id	state	prio	start_date	end_date
123	open	low	4/1/2025	4/1/2025
456	open	high	4/1/2025	4/1/2025
456	closed	high	5/1/2025	5/1/2025
789	open	medium	5/1/2025	5/1/2025

Next to this, I loaded an calendar table that is not connected to the table above.

With below measure I am able to create a trendline to show the status per month, showing 2 open items in April and 1 open item in May. 

CALCULATE(COUNT(Table[ID]), FILTER(Table[start_date] <= max(calendar[date]) && Table[end_date] >= min(calendar[date]) || Table[end_date] = blank()))

CALCULATE(COUNT(Table[ID]), FILTER(Table[start_date] <= max(calendar[date]) && Table[end_date] >= min(calendar[date]) || Table[end_date] = blank()))

I am also able to get the current status (latest) by doing a simple count on the id and filter on end_date = latest. 

What I would like to achieve is a measure that combines the two functionalities:

- Showing the current status by default 

- Showing the previous status (e.g. April (2 open items)) when selecting that month in the trendline as described above. 

How could I set that up? 

[maruthisp · Accepted solution]

Hi Janou,

As per my undestanding, there are few things to consider:

1. SCD Type 2 should be implemented in the Fact Tables by using MERGE statements/ INSERT-UPDATE statements.
2. If you are not doing in the tables levels, then need to do it in the DAX. I feel bit tricking to do it.

Please find the attached pbix file for your reference as per the sample data which you provided.

Slowly changing dimensions current status vs history.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X 

[Janou]

That worked! Thank you! 

[maruthisp]

I am glad to hear as it worked for you! 👍

[lbendlin]

What you have is fact data, not an SCD2.  

How do you plan to "select that month in the trendline" ?