cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TGS84 Visitor
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
Community Support Team
Community Support Team

Re: Dax Measure Fill Down Value

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

View solution in original post

3 REPLIES 3
Super User
Super User

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.        

Community Support Team
Community Support Team

Re: Dax Measure Fill Down Value

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

View solution in original post

JPGV Frequent Visitor
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?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)