Calculate YoY and QTD dynamically without using date characteristics

[hasmath]

Hi,

I have the data in the form of Fiscal Quarter and Amount as below. I do not have the date characteristics in the data. How to calculate the the YoY and QTD based on the Fiscal Quarter dynamically.

Fisc Qtr Amount
2018-Q1 10,000
2018-Q2 15,000
2018-Q3 18,000
2018-Q4 17,500
2019-Q1 20,000
2019-Q2 21,000
2019-Q3 18,000
2019-Q4 19,000
2020-Q1 20,000
2020-Q2 22,000

Thanks,

Hasmath

[amitchandak · Accepted solution]

The first way is you create a date like this and join it with date table

date = Date(left([Fisc Qtr],4),right([Fisc Qtr],1)*3,1)

With date calendar use following formula

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

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)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

2nd Option is

Create a Qtr table. And create a Qtr Rank and use that get formula

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank]))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),Filter(all(QTR),QTR[Rank]=Max(QTR[Rank])-1)

Refer to how week rank work. Same is true for qtr :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Greg_Deckler]

I would split out that first column into Year and Quarter columns using RIGHT and LEFT functions in calculated columns in DAX. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...