How to do DAX logic on date level from a SCD2 table

[harvis87]

Hi,

I have a large SCD2 table (9 million rows), it's mostly large because I had to (with the help of SQL) "fold out" the validfrom and validto dates to one row per date, in order to calculate if a ID is overdue or not. If I am able to do this with DAX that would really open up a lot of possibilities.

Case:

SCD2 table (resides in DWH) example of data:

For each ID and for each date I need to check if it's overdue or not, if it's overdue then flag to 1 else 0 in a [Is Overdue] column.

For instance 230932094 get this new row when [Is Overdue] = 1

The current solution is to fold out these data and do this check with SQL, and therefore I get a new validfromdate and validtodate when the flag changes from 0 to 1. 

In PBI I have DAX measures that is built on top of this logic

VAR _maxDate = MAX( dimDate[Date] )
RETURN
    IF(
        MAX( dimDate[Date] ) > _maxDate,
        BLANK( ),
        CALCULATE(
            DISTINCTCOUNT( factTable[ID] ),
            CROSSFILTER( factTable[ValidFromDate], dimDate[Date], NONE ),
            factTable[ValidFromDate] <= _maxDate
                && factTable[ValidToDate] > _maxDate
        )
    )

The Crossfilter is there since I have a relationship between factTable[ValidFromDate], dimDate[Date]

This DAX "folds out" the SCD2 table giving the trending possibilities for day to day. The problem is to build logic on the result of this DAX, like doing the [Is Overdue] "after" this DAX is applied and not in SQL.

Have some of you guru's done anything like this or have some suggestions?

[amitchandak]

@harvis87 , You can try like

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Also, check Guyinacube video on SCD

https://www.youtube.com/watch?v=tKeaQpWynzg