Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Dax, fill down in formula

Hello everybody,

 

Somebody knows, how can I use Fill Down with column formula?

I have 2 column, date and value:

1. 01/01/2017 - 500

2. 01/02/2017 - blank

3. 01/03/2017 - blank

4. 01/04/2017 - 1000

5. 01/04/2017 - blank

 need to:

1. 01/01/2017 - 500

2. 01/02/2017 - 500

3. 01/03/2017 - 500

4. 01/04/2017 - 1000

5. 01/04/2017 - 1000

 

in this situation I can't use Power Query

thanks!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous

 

Try Using this calculated column

 

New Value =
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( TableName[Date], 1 ),
        FILTER (
            ALL ( TableName ),
            TableName[Date] <= EARLIER ( TableName[Date] )
                && NOT ( ISBLANK ( TableName[Value] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( TableName[Value] ),
        FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate )
    )

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi, 

 

Using this same question as an example, does anyone knows how I can do up filling instead?

 

Meaning with date and value as below:

1. 01/01/2017 - 500

2. 01/02/2017 - blank

3. 01/03/2017 - blank

4. 01/04/2017 - 1000

 I need:

1. 01/01/2017 - 500

2. 01/02/2017 - 1000

3. 01/03/2017 - 1000

4. 01/04/2017 - 1000

 

instead.. Thanks!!

Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous

 

Try Using this calculated column

 

New Value =
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( TableName[Date], 1 ),
        FILTER (
            ALL ( TableName ),
            TableName[Date] <= EARLIER ( TableName[Date] )
                && NOT ( ISBLANK ( TableName[Value] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( TableName[Value] ),
        FILTER ( ALL ( TableName ), TableName[Date] = LastNonBlankDate )
    )

 

 

Anonymous
Not applicable

Hello, Zubair,

 

I am also looking for fill-up values solution. I carefully studied this code you suggested and it seemed to be no different from from fill-down. Could you please advice if this code is indeed different and what is the code for "fill-down" solution.  Thank you

Anonymous
Not applicable

Just wanted to share an option of a formula for fill-up, maybe someone will find it helpful:

 

New Value up =
VAR LastNonBlankDate =
CALCULATE (
FIRSTNONBLANK( 'Table'[Дата]; 'Table'[Дата]);
FILTER (
ALL ( 'Table' );
'Table'[Дата] >= EARLIER ( 'Table'[Дата] )
&& NOT ( ISBLANK ( 'Table'[Sales] ) )
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Sales] );
FILTER ( ALL ( 'Table' ); 'Table'[Дата] = LastNonBlankDate )
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.