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








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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous

    Thanks. This is very helpful. However is not showing the most recent(February) Price/Box.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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