cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

Regards
Zubair

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

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

Regards
Zubair

New Member

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

Community Champion

@Anonymous

Regards
Zubair

New Member

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors