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

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

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

 

 


Regards
Zubair

Please try my custom visuals

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

 

 


Regards
Zubair

Please try my custom visuals

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

 

1012.png


Regards
Zubair

Please try my custom visuals

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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