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.

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