Dax Measure Fill Down Value

[TGS84]

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?

[v-chuncz-msft · Accepted solution]

@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
        )
    )

[JPGV]

@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]

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.