Forum Discussion

Rohitb's avatar
Rohitb
Icon for Helper I rankHelper I
2 years ago

Extrapolation of data

Source Table : Id                  DOJ             DOR                Level        Rank Date                     1            13/08/2010                                  10          1/10/2023 ...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Rohitb ,

I create two tables as you mentioned.

Then I create two calculated columns.

Level =
VAR RankDate =
    SELECTEDVALUE ( T1[Rank Date] )
VAR PeriodEndDate =
    EOMONTH ( [Period], 0 )
RETURN
    IF (
        RankDate > PeriodEndDate,
        RELATED ( T1[Level] ) - 1,
        RELATED ( T1[Level] )
    )

Tenure = DATEDIFF(RELATED(T1[DOJ]), [Period], MONTH)

 

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.