Forum Discussion
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...
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
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