topic Re: Extrapolation of data in DAX Commands and Tips

Extrapolation of data

Rohitb — Fri, 03 May 2024 10:36:59 GMT

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 ?

Re: Extrapolation of data

Anonymous — Mon, 06 May 2024 06:03:13 GMT

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.

Re: Extrapolation of data

NandanHegde — Mon, 06 May 2024 08:27:44 GMT

Anonymous 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

Re: Extrapolation of data

Rohitb — Mon, 06 May 2024 09:32:12 GMT

Hello Anonymous

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

Re: Extrapolation of data

Anonymous — Fri, 10 May 2024 06:19:46 GMT

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