Display Previous FY Year Quarter when previous year is Q4

[Anonymous]

Hey community. 

I am trying to display 2 values in 2 cards - the most recent fiscal year and quarter, and then the previous fiscal year and quarter. The most recent fiscal year & quarter is FY2021 Q1. The formula I used is

Most Recent Fiscal Year & Quarter = MAX ( 'Dates'[FY Year Quarter] )

The previous Fiscal Year will be FY2020 Q4.

What would be the DAX formulaic way to obtain the most recent Fiscal Year Quarter and the previous one? I wanted to use my Fiscal Year Quarter index and just subtract 1 but I wasn't sure how to subtract 1 from my Fiscal Year Fiscal Quarter Index but display another column. Currently I get the below. Smaple data set posted in link below

Link to sample dataset

https://drive.google.com/drive/folders/1V91FntSmy33fDwO2SHSIVe1xdBryQb-b?usp=sharing

[amitchandak]

@Anonymous , for standard qtr , you can use datesqtd

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

for nonstandard/FY Qtr use Rank way used for week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

FY Qtr of your choice

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

Power BI — QTD Questions — Time Intelligence 2–5
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can create a measure

Previous Fiscal Year & Quarter = CALCULATE(MAX(Dates[FY Year Quarter]),FILTER(Dates,Dates[Date] = MAX(Dates[Date]) - 90))

Regards,

Harsh Nathani