Forum Discussion

richanthony's avatar
richanthony
Helper I
4 years ago
Solved

Slowly changing fact table

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...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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