Create a column with value from calculated column for prior quarter

[Anonymous]

I'm relatively new to Power BI and I'm struggling to figure out the appropriate DAX formula to achieve the following:

I want to create a column that retrieves the prior quarter's value for a measure called "% of Target" so that I have a column for the current quarter's % of target and a column for the prior quarter's % of target.  My table has 100's of items that show values for 12 quarters so I want to use the values in the columns at the row-level in my reports.

The table below shows the last column, which is the one I wanted to generate.

Title	Target	Progress	Unit	Period End	% of Target	Prior Qtr - % of Target
Associate engagement score	81.0	81.0	unit	3/31/2021	100%
Associate engagement score	81	82	unit	6/30/2021	101%	100%
Cloud Training Certifications	100.0	98.0	%	3/31/2021	98%	101%
Cloud Training Certifications	100	94	%	6/30/2021	94%	98%
Expense	56231.0	42000.0	$	3/31/2021	75%	94%
Expense	$          96,532	$       94,545	$	6/30/2021	98%	75%
Interlock Revenue Attainment	100.0	100.0	%	3/31/2021	100%	98%
Interlock Revenue Attainment	100	90	%	6/30/2021	90%	100%
Net Promoter Score (NPS)	59.0	57.0	unit	3/31/2021	97%	90%
Net Promoter Score (NPS)	60	61	unit	6/30/2021	102%	97%
New Logos	3.0	1.0	unit	3/31/2021	33%	102%
New Logos	5	4	unit	6/30/2021	80%	33%
On-time Solution Implementation	100.0	100.0	%	3/31/2021	100%	80%
On-time Solution Implementation	100	95	%	6/30/2021	95%	100%
Product Quality – Avg # Issues	8.8	6.0	unit	3/31/2021	68%	95%
Product Quality – Avg # Issues	8.4	8.4	unit	6/30/2021	100%	68%
Product Quality – R&D Completion	82.0	82.0	%	3/31/2021	100%	100%
Product Quality – R&D Completion	84	82	%	6/30/2021	98%	100%
Product Quality – UDL Clean Run	96.0	96.0	%	3/31/2021	100%	98%
Product Quality – UDL Clean Run	96	96	%	6/30/2021	100%	100%
Revenue	92112.0	920444.0	$	3/31/2021	999%	100%
Revenue	$          90,223		$	6/30/2021	0%	999%
Solution Roadmap On-time completion	100.0	100.0	%	3/31/2021	100%	0%
Solution Roadmap On-time completion	100	88	%	6/30/2021	88%	100%

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You can create a Calculated column as the following.

Prior Qtr - % of Target =

VAR cloest_title =

    MAXX (

        FILTER ( 'Table', 'Table'[Title] < EARLIER ( 'Table'[Title] ) ),

        'Table'[Title]

    )

VAR _min =

    MAXX (

        FILTER (

            'Table',

            'Table'[Title] = EARLIER ( 'Table'[Title] )

                && 'Table'[Period End] < EARLIER ( 'Table'[Period End] )

                || 'Table'[Title] = cloest_title

                    && 'Table'[Period End] > EARLIER ( 'Table'[Period End] )

        ),

        [% of Target]

    )

RETURN

    _min

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!

[amitchandak]

@Anonymous , Try a new column like

New column =
var _min = maxx(filter(Table, [Period end] <earlier([period end])),[period end])
return
maxx(filter(Table, [Period end] = _min),[% of Target])