Dax, fill down in formula

[Anonymous]

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!

[Anonymous]

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 · Accepted solution]

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]

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

[Zubair_Muhammad]

@Anonymous

[Anonymous]

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 )

)