Forum Discussion

ForzaMami's avatar
ForzaMami
Regular Visitor
4 years ago

one top row data

It's a little hard to explain, I need to use the same formula in a single dax formula, the one in the top row. I'm trying to power the "Exponential Smoothing" column in the excel below. Is something like this possible ?

Thanks

11 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ForzaMami 

    you may try

     

    Exponential Smoothing =
VAR CurrentYearMonth =
    MAX ( Table[Year_Month] )
VAR PreviousStatus1 =
    CALCULATE ( [Status], Table[Year_Month] = CurrentYearMonth - 100 )
VAR PreviousStatus2 =
    CALCULATE ( [Status], Table[Year_Month] = CurrentYearMonth - 200 )
RETURN
    IF (
        NOT ISBLANK ( PreviousStatus1 ) && NOT ISBLANK ( PreviousStatus2 ),
        IF (
            ISBLANK ( PreviousStatus1 ),
            PreviousStatus2,
            0.5 * PreviousStatus1 + 0.5 * PreviousStatus2
        )
    )

     

    • ForzaMami's avatar
      ForzaMami
      Regular Visitor
      4 years ago

      Hi,

       

      Dont work. If you want to get the 1st and 2nd months retrospectively, it doesn't work either.

       

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        ForzaMami 

        Can you please right the expected results with explanation in this same screenshot?

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      ForzaMami 
      My dear, I think the thing you somehow missed is that DAX does not support recursive calculations. Meaning that you cannot by any means refer to the previous raw of the same column under evaluation. In some cases, the same results can be obtained using different approach. 
      In this community there are people who are willing to spend time and effort to help others sometimes having no idea about their data expecting some cooperation to clarify it out and help them find a solution. 

      My question to you is there any way we can calculate the values depending only on the Status column? Can you transform your equation to a one where all variables belong to the status column only? (no matter how complex this equation would be)