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