Forum Discussion

micsafdas's avatar
micsafdas
Frequent Visitor
6 years ago
Solved

Previous Year average and 2 year average

Hello! I'm stuck and it would be great if you Experts could help me.   I do have a measure which calculates the monthly rents. What I would need is a average bar for the last and the second to las...
Need Help
Tutorial Requests
  • micsafdas's avatar
    micsafdas
    6 years ago

    I managed to solve it my self, once I got inspired by amitchandak's solution. 🙂

     

    Its

    Average last year = CALCULATE(SUM('Erlöse'[Betrag])/12;PREVIOUSYEAR(Datumstabelle[Date]);'Erlöse'[Positionsart] IN { "Miete" } )

    and

    Average second to last year = CALCULATE(SUM('Erlöse'[Betrag])/12;PARALLELPERIOD(Datumstabelle[Date];-2;YEAR);'Erlöse'[Positionsart] IN { "Miete" } )

     

    Simple as that... ^^

     

    Thanks all

micsafdas's avatar
micsafdas
Frequent Visitor
6 years ago

amitchandak 

Thanks for your post! This seems promising, but I didn't get the expected result back.

I probably explained it not to the fully extent.

 

I need (looking from 2019) the average of 2018, namely from 01.01.18 - 31.12.18 and from 2017 (vice versa)

No year to date calculations.

 

Btw, I do have a date table. To give it some "flesh", I uploaded the basic pbix which translates to the big pbix.

https://www.dropbox.com/s/u4wmq7puu5mzl05/Showcase.pbix?dl=0

 

I would expect the "Previous Year Average" to be at 4333 in 2018, as the sum of 2017 is 52K.

 

v-kelly-msft 

Thanks also!

I couldn't get your code to work though. It doesn't seem to understand this part:

Datumstabelle[Date].[Year]

The [Year] seems to be the problem. Why so?

 

Thanks so far you two!

 

micsafdas's avatar
micsafdas
Frequent Visitor
6 years ago

I managed to solve it my self, once I got inspired by amitchandak's solution. 🙂

 

Its

Average last year = CALCULATE(SUM('Erlöse'[Betrag])/12;PREVIOUSYEAR(Datumstabelle[Date]);'Erlöse'[Positionsart] IN { "Miete" } )

and

Average second to last year = CALCULATE(SUM('Erlöse'[Betrag])/12;PARALLELPERIOD(Datumstabelle[Date];-2;YEAR);'Erlöse'[Positionsart] IN { "Miete" } )

 

Simple as that... ^^

 

Thanks all