Create desired matrix based on Prior Month values

[A123-Chauhan]

hi there !

I have 5 years of data for various LOBs in below format (left side). 

Desired Matrix (On the right side)

Requirement : Based on the above data, need to create a page in the report that will have Year and Month Slicer and one matrix.

Matrix should contain LOB name, timeframe, data of selected Month, Previous year and Previous Quarter Month End data. (Refer above matrix examples)

For an instance : If user Select May 2023 and LOB A from slicers, matrix should look like below :

LOB	Time Frame	AR	Over 60 AR	Over 90 AR
A	Current Month	AR value for May 2023	Over 60 AR value for May 2023	Over 90 AR value for May 2023
A	Previous Quarter Month	AR value for Mar 2023	Over 60 AR value for Mar 2023	Over 90 AR value for Mar 2023
A	Previous Year	AR value for May 2022	Over 60 AR value for May 2022	Over 90 AR value for May 2022

If user Select June 2023 and LOB B from slicers, matrix should look like below :

LOB	Time Frame	AR	Over 60 AR	Over 90 AR
B	Current Month	AR value for June 2023	Over 60 AR value for June 2023	Over 90 AR value for June 2023
B	Previous Quarter Month	AR value for Mar 2023	Over 60 AR value for Mar 2023	Over 90 AR value for Mar 2023
B	Previous Year	AR value for June 2022	Over 60 AR value for June 2022	Over 90 AR value for June 2022

There is a catch for Previous Quarter Month Calculation :

The Previous Quarter Month  has to be calculated based on selected value of slicer.  It should be the previous Quarter End Month.

 FY starts from July and ends in June. So technically, Quarter End Months are : September (Q1 end Month), December(Q2 end Month), March(Q3 End Month) and June(Q4 End Month)

For example : If we select Apr 2023 or May 2023 or June 2023,  Previous Quarter Month should point to March 2023

Similarly , If we select July 2023, Aug 2023, Sep 2023, Previous Quarter Month value should point to June 2023

Can we achieve this requirement in Power BI ?