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