Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need Help: Persistent Moving/Rolling Average

Is there a DAX formula to persist, or hold, the lastest value of the rolling/moving average whenever data points are over and to extend it thru time? 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    Anonymous I'm sorry for that.
    I'll try and see if I can get LastNonBlank to work. I propose an alternative solution. 
    Get the lastdate where price/box is not blank. Look up the price/box for that date.
    =
VAR lastPriceDate =
    CALCULATE (
        MAX ( Table1[Date] );
        ALLEXCEPT ( Table1; Table1[Product] );
        NOT ( ISBLANK ( Table1[Price/Box] ) )
    )
VAR LastPrice =
    CALCULATE (
        MAX ( Table1[Price/Box] );
        ALL ( Table1 );
        VALUES ( Table1[Product] );
        Table1[Date] = lastPriceDate
    )
RETURN
    IF ( ISBLANK ( Table1[Price/Box] ); LastPrice; Table1[Price/Box] )



    Cheers,
    Pedro








5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Hi J-Saw,

     

    Maybe something like LASTDATE() or LASTNONBLAMK(), if can't meet your requirement, please share some sample data and clarify more details about your requirement or logic.

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-yuta-msft

      Date                    Product   Boxes       Prices      Price/Box

      2018-01-011710$1.43
      2018-01-012512$2.40
      2018-02-011910$1.11
      2018-02-012612$2.00
      2018-03-011   
      2018-03-012   

       

      So basically would be having the Price/Box for 2018-03-01 using the most recent Price/Box for product 1 and 2.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        If what you're looking for is a calculated column, you can do the following:

        =
IF (
    ISBLANK ( Table1[Price/Box] );
    CALCULATE (
        LASTNONBLANK ( Table1[Price/Box]; 1 );
        ALLEXCEPT ( Table1; Table1[Product] )
    );
    Table1[Price/Box]
)

        Last Price

         

        To make the "Last Price" clear I chose to put in datetime information.