Start and End dates in a type 2 SCD table and DAX for slicer

[Power-BI-User]

Hi everyone,

I am trying to convert a history revision table into a type 2 SCD table, which can then be used for dynamic slicing through a slider visual. Being new to Power BI and DAX, I am facing some issues when trying to reformat the table.

Here's a sample of what my table looks like:

version	UID	date	operation	surrogate key	first date	last date
1	OBJECT_X	26/06/2021	CREATE	585	26/06/2021	26/06/2021
2	OBJECT_X	28/06/2021	UPDATE	1556	28/06/2021	28/06/2021
3	OBJECT_X	28/06/2021	UPDATE	2610	28/06/2021	28/06/2021
4	OBJECT_X	28/06/2021	UPDATE	2642	28/06/2021	28/06/2021
5	OBJECT_X	2/07/2021	UPDATE	3246	2/07/2021	2/07/2021
6	OBJECT_X	2/07/2021	UPDATE	3276	2/07/2021	2/07/2021
7	OBJECT_X	2/07/2021	UPDATE	4621	2/07/2021	2/07/2021
8	OBJECT_X	2/07/2021	UPDATE	4652	2/07/2021	2/07/2021
9	OBJECT_X	15/07/2021	UPDATE	5751	15/07/2021	15/07/2021
10	OBJECT_X	17/08/2021	UPDATE	23185	17/08/2021	17/08/2021

I got the columns version, UID, date and operation from the data source. Column surrogate key is just an index column created in Power BI. 

Column start date is just copying the date column values, using the following DAX code -

First Date = CALCULATE(MIN('SCD-Test'[date]), ALLEXCEPT('SCD-Test','SCD-Test'[Surrogate Key]))

I tried to copy the values in date for the same UIDs but with the next version number to fill out last date, but my code didn't work -

Last Date = CALCULATE(MIN('SCD-Test'[date]), 'SCD-Test'[version] + 1)

However, this just copied the date column again. What is the proper way to get the dates for the next version numbers for every UID?

Also, taking performance and file size into consideration, it would be better to create these columns during the data preprocessing stage instead. The first date column can be created using the GUI, but what would be the M code to create the last date column?

The second thing I want to know is how to write the DAX code to get the number of active UIDs in a specific time period. I know the algorithm, but I cannot convert it into DAX.

Count rows where (the start date is less than or equal to start date on slicer) and (end date is greater than end date on slicer) and (operation is not DELETE)

One thing that I am struggling with in general is performing operations in DAX to get aggregated stats for every categorical value in a column or based on some condition. I ran the following commands to try and understand the basics, but I didn't get the results I was expecting:

CALCULATE (COUNTROWS('SCD-Test'), ALLEXCEPT ('SCD-Test', 'SCD-Test'[version]))

CALCULATE (COUNTROWS('SCD-Test'), VALUES('SCD-Test'[version]))

Both returned the total count of all rows in the data, not the counts segregated by version numbers.

[V-lianl-msft · Accepted solution]

Hi @Power-BI-User ,

Try to create an index column and then a custom column in the query editor:

= try #"Added Index" [date] { [Index]  + 1 } otherwise null

Get the number of active UIDs in a specific time period:

Measure = CALCULATE(COUNT('Table (3)'[UID]),FILTER('Table (3)','Table (3)'[first date]>=MIN('date'[Date])&&'Table (3)'[last date]<=MAX('date'[Date])||ISBLANK('Table (3)'[last date])))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power-BI-User]

Hi Liang,

Thank you very much for your help. The DAX code gives me the exact output I want. 🙂

Cheers.