Extrapolation of data

[Rohitb]

Source Table :

Id                  DOJ             DOR                Level        Rank Date                    
1            13/08/2010                                  10          1/10/2023
2            26/12/2022      12/12/2023          9         
3            22/09/2023                                   8           1/08/2023
4            12/11/2023      30/11/2023          9
5            12/01/2020      05/03/2022          12

Output I need ( I need to extrapolate the data from Fiscal year start till date based on DOJ )

Requirements: I need to derive 4 columns Period,Id,Level & Tenure
Where Period values would be from Sep-23,Oct-23......Aug-24 (current fiscal year months) 

Id would be those values in the source table whose DOJ falls in that period

Level is calculated based on Rank date (in case if the rank date is greater than the period month end then Level = Level - 1 else same Level )

Tenure is Period - DOJ for the corresponding id.

Period      Id               Level                                                                    Tenure
Sep-23       1             10-1 =9 ( as rank date > Sep 23)                         Sep-23 - DOJ
Sep-23       2             9
Sep-23       3             8   ( as rank date < sep 23, keep it as is)
Oct-23       1             10                                                       Oct-23 - DOJ
Oct-23       2              9
Oct-23       3             8
Nov-23       1            10
Nov-23       2             9  
Nov-23       3             8
Nov-23       4             9
Dec-23       1             10
Dec-23       2             9   
Dec-23       3             8

How to acheive this extrapolation output as a table in PowerBI ?

[Rohitb]

Hello @v-yilong-msft 

Thankyou for your response.
I need to derive the 1st table as stated by user @NandanHegde .
I am not sure how to do that

[v-yilong-msft]

Hi @Rohitb ,

Do you mean converting to the first table based on the second table? Can you provide me with more information on this? As it stands I can't see any obvious correlation between the two tables.

Best Regards

Yilong Zhou

[NandanHegde]

@v-yilong-msft I guess the ask from the user is to generate the 2 table that you have already created.
How to extrapolate to the below table from the source seems to be his ask ? and also derive other columns

I am not sure, but we can create a calendar table and then use cross join filter condition to derive it but I might be wrong here

[v-yilong-msft]

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.