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.

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.