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]))   ...
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

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)
       

       

      • mariussve1's avatar
        mariussve1
        Solution Sage
        3 years ago

        Hi again 🙂

        Could you email your .pbix file to marius(dot)sve1(a)gmail(dot)com ?

        Marius