Slowly changing fact table

[richanthony]

I am looking to build a datset on top of a slowly changing fact table, this is not a major problem but I am a bit stuck on the best solution for one specific business question we are trying to report on. We need to show daily trends of what status the record was on any given day. If the fact table was a daily snapshot this would be easy. 

I have a working solution to "fill in the dates" between the record start date and end date but it becomes very slow and not easy use incremental refresh on. 

Has anyone had a similar challenge and found a solution?

Each record in the Fact table is tagged with a start date time the endate time and a flag to highlight if the record is the current one. 

Record_Start_Datetime	Record_End_Datetime	Record_Current_Flag
2022-07-26 01:03:20	2022-07-26 01:03:20	0
2022-07-26 01:04:20	2099-01-01 12:00:00	1

[v-jingzhang]

Hi @richanthony 

Can you please share more sample data that include multiple dates? Does this table include only one record or multiple records? What is the status you want to display and how do you want to show the daily trends of it? If the table has data for every day, does every day have a flag with value 1 to mark the status of that day? If every day has a flag value 1, can we take that row's status for that day? 

Best Regards,
Community Support Team _ Jing

[richanthony]

@BA_Pete thanks i'll try that out. 

[BA_Pete · Accepted solution]

Hi @richanthony ,

Not sure exactly what you mean by "what status the record was on any given day", but a fairly basic measure structure for use on SCD's would be something like this:

_noofRecordsAtDate =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    COUNTROWS(yourSCDTable),
    __cDate >= yourSCDTable[startDate],
    __cDate <= yourSCDTable[endDate]
)

It's really just a case of changing the required calculation/result within the calculate, and giving the measure a date context from the calendar table.

Pete