Measure to calculate Prior Year without Date Logic

[jpk564]

Hello - I want to create a measure that easily calculates the prior year units (example in orange), with some sort of lookup, referencing prior fiscal quarter. I have a dataset with fiscal quarter, that quarter's prior year fiscal quarter, and units.

I don't want to use date logic, and I want this to work if one of the fiscal quarters is filtered on, which none of my previous attempts have been able to do. Thanks!

Fiscal Quarter	Prior Year Fiscal Quarter	Units	Prior Year Units
2019-Q1	2018-Q1	9.09
2019-Q2	2018-Q2	22.97
2019-Q3	2018-Q3	95.96
2019-Q4	2018-Q4	60.30
2020-Q1	2019-Q1	34.03	9.09
2020-Q2	2019-Q2	81.67	22.97
2020-Q3	2019-Q3	82.67	95.96
2020-Q4	2019-Q4	18.13	60.30

[amitchandak]

@jpk564 , Your qtr are in sortable format Create a new table with distinct Fiscal Quarter

Date= distinct(Table[Fiscal Quarter])

Also add column

Year = left([Fiscal Quarter],4)

Qtr no = right([Fiscal Quarter],1)

In this table add rank column

Qtr Rank = RANKX(all('Date'),'Date'[Fiscal Quarter],,ASC,Dense)

Now try measures like

This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Last year same Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4))

or

Last Year Same QTR = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Qtr no] = Max('Date'[Qtr no])))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0