Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average between Years

Hi Guys, peace. 

 

I have a situation that I don't know how to solve. 

 

So, I need to calculate the average between the years and bring this information fixed as a example below, in column MD LY I almost reached my target, but it's bringing an average between the 3 years and I need to do each year separated. 

I've tryed a lot of manners to do it, but I couldn't find a solution yet. Please, if anyone have a tip or idea to solve it, would be great. 

 

 

 

Best Regards 

Clever

18 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , You need to try a measure like

     

    Rolling 3 = calculate(AverageX(Values('Date'[Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,Year))

     

    replace sum sales amount with your measures , sum till year level and then avg.

     

    Avg above month level

    Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,Year))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, good night, peace


      I've tryed with Sum, but it's not working. 
      But thanks

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    what exactly is your expected result?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi @Cleverbro, 

    According to your description, you want to calculate the average between the years, right? Here are my steps you can follow as a solution. 

     

    (1)If you want to calculate the average by year, you can filter directly in the "Columns" section. 

     

     

     (2)Then the result is as follows. 

     

    If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. 

     

    Best Regards, 

    Neeko Tang 

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous 
      I've tryed but this option don't shows to me. 
      tamerj1 
      Tamer, I'm doing a caculation in each line of my database,

      so I want to bring a Avg Price Unit per Year Total like the colunm MD below and this be fixed like in the Formula MD in the 1ªScreenShot ,

      But when I'm seing per month in the 2ªScreenshot, I've differents prices and they shoud be the same value as the 1ªScreenShot fixed (MD)

       

      1ªScreenshot

      But We can see that each month I've different's Price Unit


      2ªScreenShot

      amitchandak 

       

      The only point is that I'm using formulas to reach in my price unit, like below. 

      So when I try to use Sum, it's not allowed. 

       

       

      Do we have another Alternative? 

       

      Thanks

      Clever

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Anonymous 

        Seems you have multiple queries. Let start with this

        Price Unit EUR =
        CALCULATE (
        DIVIDE ( [Total Costs EUR], [Volume Total KG] ),
        ALLEXCEPT ( 'Date', 'Date'[Year] )
        )