Forum Discussion

hasmath's avatar
hasmath
Frequent Visitor
6 years ago
Solved

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 dynamic...
Show and Tell
  • amitchandak's avatar
    amitchandak
    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