Need help in data transformation.

[Bansi008]

Hi there,

I have the below sample dataset, which i need to transform in such a way that I can create a few more columns for quarter-to-quarter date comparison.   Can someone please guide how would i do that. 

below my sample dataset :-

NAME	DATE	ROR	TVPI
FUND 1	6/30/2024	10%	1.1x
FUND 1	3/31/2024	15%	1.3x
FUND 1	12/31/2024	17%	0.6x
FUND 2	6/30/2024	20%	1.3x
FUND 2	3/31/2024	18%	1.8x
FUND 2	12/31/2024	25%	0.9x
FUND 3	6/30/2024	2%	1.45x
FUND 3	3/31/2024	3%	1.2x
FUND 3	12/31/2024	4.50%	2.3x

Expected output table:-

NAME	LATEST Q-END DATE	PRIOR Q-END DATE	LATEST Q-END ROR	PRIOR Q-END ROR	LATEST Q-END TVPI	PRIOR Q-END TVPI
FUND 1	6/30/2024	3/31/2024	10%	15%	1.1x	1.3x
FUND 1	3/31/2024	12/31/2024	15%	17%	1.3x	0.6x
FUND 2	6/30/2024	3/31/2024	20%	18%	1.3x	1.8x
FUND 2	3/31/2024	12/31/2024	18%	25%	1.8x	0.9x
FUND 3	6/30/2024	3/31/2024	2%	3%	1.45x	1.2x
FUND 3	3/31/2024	12/31/2024	3%	5%	1.2x	2.3x

[Gabry · Accepted solution]

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.

[Srini_dev · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[Srini_dev · Accepted solution]

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

[Gabry · Accepted solution]

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.