Forum Discussion

MTrullàs's avatar
MTrullàs
Helper III
3 years ago
Solved

Average 7 last inputs

I have a PBI with a metric for calculate inputs.

 
ENTRADAS =
     CALCULATE(
    [Coches],
        USERELATIONSHIP(
        STOCK_EVOLUTION[Fecha_entrada_calculada],'DimDate'[Date]))
 


Now I need a metric to calculate the average of the 7 last inputs .

 

For example, screenshot, for the day 02/02/2023 the an rounded to the nearest whole number average of the last 7 days is 55. Please note 28/02/2023 and 29/02/2023 there are no inputs.

 

Many thanks for your answers

 

Need Help
Tips and Tricks
  • mariussve1's avatar
    mariussve1
    3 years ago

    Hi again MTrullàs , 

    Not so easy to guess what your [Coches] measure do, but could you please give this a try?

    If we say that [Coches] = SUM ( Table[Column] )

    Then you can try this:

    PROMEDIO_ENTRADAS_7 =
    VAR __LastDate =
        MAX ( STOCK_EVOLUTION[Date] )
    VAR __Last7dates =
        TOPN ( 7, FILTER ( ALL ( STOCK_EVOLUTION[Date] ), STOCK_EVOLUTION[Date] <= __LastDate ), STOCK_EVOLUTION[Date], DESC )
    VAR __Result =
        CALCULATE ( AVERAGEX( STOCK_EVOLUTION, [Entradas] ), __Last7dates )
    RETURN
        __Result
     

    This works for me, so if you cant make it work now, then I can send you an example pbix file.


    Br

    Marius

5 Replies

  • mariussve1's avatar
    mariussve1
    Solution Sage
    3 years ago

    Hi,

     

    Could you try this:

     

    AVERAGEX(TOPN(7,'Table','Table'[Date],DESC),[ENTRADAS])

     

    Br

    Marius

  • MTrullàs's avatar
    MTrullàs
    Helper III
    3 years ago

    Thank you very much mariussve1  for you answer!

     

    Something is wrong, because it dosen't work as we can see in the screenshot. thank you very much for your help!

     

     

     

    • mariussve1's avatar
      mariussve1
      Solution Sage
      3 years ago

      Hi again MTrullàs , 

      Not so easy to guess what your [Coches] measure do, but could you please give this a try?

      If we say that [Coches] = SUM ( Table[Column] )

      Then you can try this:

      PROMEDIO_ENTRADAS_7 =
      VAR __LastDate =
          MAX ( STOCK_EVOLUTION[Date] )
      VAR __Last7dates =
          TOPN ( 7, FILTER ( ALL ( STOCK_EVOLUTION[Date] ), STOCK_EVOLUTION[Date] <= __LastDate ), STOCK_EVOLUTION[Date], DESC )
      VAR __Result =
          CALCULATE ( AVERAGEX( STOCK_EVOLUTION, [Entradas] ), __Last7dates )
      RETURN
          __Result
       

      This works for me, so if you cant make it work now, then I can send you an example pbix file.


      Br

      Marius

      • MTrullàs's avatar
        MTrullàs
        Helper III
        3 years ago

        Hello mariussve1 

         

        Thank you very much for your answer. 

         

        Yes, you have right, I had had give more datiles.

         

        I think we are close to the solution, but it dosen't work yet.

         

        I think that the problem is that [Coches] isn't [Coches] = SUM ( Table[Column] ). In my PBI [Coches] is

        Coches = COUNT(STOCK_EVOLUTION[Numero Bastidor]).
         
         So, the result with your metric in my PBI is a wrong mesure that we can see in the screenshot.
         
        Thank you very much for your time!
        If with this isn't enough I share all PBIX file. ( I have to look for how can I do it)