Forum Discussion
Calculate YoY and QTD dynamically without using date characteristics
- 6 years ago
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
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