Forum Discussion

ashamsuzzoha's avatar
ashamsuzzoha
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Same Period Last N Years

Is there a function that resembles SAMEPERIODLASTYEAR but that can be expanded to more than one year back? Like the average of a monthly value for the same month the last five years?

 

Thanks,

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi ashamsuzzoha ,

     

    check this out.

    PBIX

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

14 Replies

    • ashamsuzzoha's avatar
      ashamsuzzoha
      Icon for Advocate II rankAdvocate II
      6 years ago

      Thanks for the reply, mwegener.

      I think I asked my question wrong. I would like the average of the previous 5 years, not just the value from 5 years ago. Is there a function that can do that?

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi ashamsuzzoha 

         

        try this

         

        Measure =
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && DATEADD ( 'Calendar'[Date], 5, YEAR ) >= MAX ( 'Calendar'[Date] )
    )
)

        Regards,

        Marcus

        Dortmund - Germany
        If I answered your question, please mark my post as solution, this will also help others.
        Please give Kudos for support.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    datemtd, datesqtd and datesytd , you can use . By changing the number in dateadd, you can go in past as much as you want

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD Sales 5 year back = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-5,YEAR)))

last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date Filer])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date Filer],-12,MONTH),"8/31")))


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

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

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-1,Year)))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601