Need help in data transformation.

Hello Bansi008 

I’m a bit confused about the sample data you provided, and I’m having trouble understanding it fully.

In your transformed table, I noticed that in line 2, the LATEST Q-END ROR is 15%, which corresponds to the ROR for 03/31/2024, and that makes sense. However, I’m puzzled as to why the prior Q-END date is listed as 12/31/2024. Isn't 12/31/2024 after 03/31/2024? Why is it considered "prior"?

If this is just an error in the sample data, I’ve created a sample PBIX file with the calculated columns you need, which is attached. Please have a look and let me know if it works for you.

I worked out a quick solution for posted issue. I made a small change with the data (change 2024/12/31 to 2023/12/31) which I assumed was supposed to be. I know this isnt the efficient one, but rather a simple solution. Please review the attached .pbi link for more info.

link to pbi - sample_solution_1.pbix

Hi Bansi008 ， hello Srini_dev and Gabry, thank you for your prompt reply!

Based on your requirements, try as follows:

• Create an index column from 1 in power query as shown below:

• Then create the following measures with the same logic, simply replacing the column names:

PRIOR Q-END DATE = 
VAR CurrentIndex = MAX('Table'[Index])
VAR NextRowValue =
   CALCULATE(
       MAX('Table'[DATE]),
       FILTER(
           ALL('Table'),
           'Table'[Index] = CurrentIndex + 1 &&
           'Table'[Name] = MAX('Table'[Name])
       )
   )
RETURN
   IF(NOT(ISBLANK(NextRowValue)), NextRowValue, BLANK())

PRIOR Q-END ROR = 
VAR CurrentIndex = MAX('Table'[Index])
VAR NextRowValue =
   CALCULATE(
       MAX('Table'[ROR]),
       FILTER(
           ALL('Table'),
           'Table'[Index] = CurrentIndex + 1 &&
           'Table'[Name] = MAX('Table'[Name])
       )
   )
RETURN
   IF(NOT(ISBLANK(NextRowValue)), NextRowValue, BLANK())

PRIOR Q-END TVPI = VAR CurrentIndex = MAX('Table'[Index])
VAR NextRowValue =
   CALCULATE(
       MAX('Table'[TVPI]),
       FILTER(
           ALL('Table'),
           'Table'[Index] = CurrentIndex + 1 &&
           'Table'[Name] = MAX('Table'[Name])
       )
   )
RETURN
   IF(NOT(ISBLANK(NextRowValue)), NextRowValue, BLANK())

Result for your reference:

Best regards,

Joyce

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