cancel
Showing results for
Did you mean:
Visitor

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Dax Measure Fill Down Value

```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
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User III

## Re: Dax Measure Fill Down Value

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.

Highlighted
Community Support Team

## Re: Dax Measure Fill Down Value

```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
)
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Dax Measure Fill Down Value

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors