Forum Discussion
Calculate YoY and QTD dynamically without using date characteristics
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
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
2 Replies
- Greg_Deckler
Community Champion
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-TITHW/m-p/434008 - amitchandak
Super User
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
