Forum Discussion

TGS84's avatar
TGS84
Regular Visitor
7 years ago
Solved

Dax Measure Fill Down Value

I have two measures which calculate a value for a year. See following table.

My goal is to fill the gaps for measure B with the last available value. The result should look like:

 

Which DAX Construct can be used to solve my problem?

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    TGS84,

     

    You may add an additional measure.

    Measure =
VAR y =
    SELECTEDVALUE ( Table1[Year] )
RETURN
    CALCULATE (
        [Measure B],
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1[Year] ),
                Table1[Year] <= y
                    && NOT ( ISBLANK ( [Measure B] ) )
            ),
            Table1[Year], DESC
        )
    )

3 Replies

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

    TGS84,

     

    You may add an additional measure.

    Measure =
VAR y =
    SELECTEDVALUE ( Table1[Year] )
RETURN
    CALCULATE (
        [Measure B],
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( Table1[Year] ),
                Table1[Year] <= y
                    && NOT ( ISBLANK ( [Measure B] ) )
            ),
            Table1[Year], DESC
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-chuncz-msft This just solved a big problem for me, thanks!

       

      However, the measure is not giving me a total. I think I understand why it does not, but I don't know how to solve it.

       

      I modified the formula in this way:

       
      Measure =
      VAR y =
      SELECTEDVALUE ( 'dim DimDate'[DATE] )
      RETURN
      CALCULATE (
      [Backlog Previous Day],
      TOPN (
      1,
      FILTER (
      ALLSELECTED ( 'dim DimDate'[DATE] ),
      'dim DimDate'[DATE] <= y
      && NOT ( ISBLANK ( [Backlog Previous Day] ) )
      ),
      'dim DimDate'[DATE], DESC
      )
      )

       

      It is giving me exactly what I needed on a row level, but won't return a total. Is there a way to modify the measure?

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi TGS84

     

    What is the code for your [Measure B]?

    One way would be to recreate what happens in the matrix within the measure, i.e. by building a dynamic table with the years in the first column and measure B in the second column and then look for the LASTNONBLANK. But depending on the code for your measure it might be simpler than that. That is why i am asking you to show it.