How to fill up/down the blanks in a calculated column of data model (Not in Power Query)

[MaulikG7]

All the dates after 1sy July-22 shall have 1st july -22 . THis is a lookup value column in an crossjoin table.

[v-jingzhang]

Hi @MaulikG7 

There is no Fill down/Up feature for calculated columns, so you need to add a new column. You can use the following DAX

new column = 
MAXX (
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && NOT ( ISBLANK ( 'Table'[TransferWEF] ) )
    ),
    'Table'[TransferWEF]
)

Or if the column to Fill Down/Up does not have date values, you can try

new column =
VAR _date =
    MAXX (
        FILTER (
            'Table',
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && NOT ( ISBLANK ( 'Table'[TransferWEF] ) )
        ),
        'Table'[Date]
    )
RETURN
    MAXX ( FILTER ( 'Table', 'Table'[Date] = _date ), 'Table'[TransferWEF] )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[MaulikG7]

This does half the job... If the employee code changes, then i want it to restart the logic. Right now it brings last non blank value to other employees as well. I tried to add one more condition (code = earlier code), but it is not working.

[v-jingzhang]

Hi @MaulikG7 

You may try this

new column = 
MAXX (
    FILTER (
        'Table',
        'Table'[Code] = EARLIER('Table'[Code])
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && NOT ( ISBLANK ( 'Table'[TransferWEF] ) )
    ),
    'Table'[TransferWEF]
)

Or

new column = 
VAR _date =
    MAXX (
        FILTER (
            'Table',
            'Table'[Code] = EARLIER('Table'[Code])
                && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && NOT ( ISBLANK ( 'Table'[TransferWEF] ) )
        ),
        'Table'[Date]
    )
RETURN
    MAXX ( FILTER ( 'Table', 'Table'[Code] = EARLIER('Table'[Code]) && 'Table'[Date] = _date ), 'Table'[TransferWEF] )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[MaulikG7]

In case this gives more clarity, this is what i want..

[v-jingzhang · Accepted solution]

Hi @MaulikG7 

I created a sample file. You can download the attachment to see details. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[MaulikG7]

Yes. That does work. I checked it from my end as well. Will close this thread as it is solved however this measure is causing that forever 'working on it' issue as my calculated column has around 11 million rows.

[MaulikG7]

Will this work if we remove WEF date 1st July from emp code 2 then for the period 1st July to 4th July it shall give blank in the new column. I will try and let u know.

[amitchandak]

@MaulikG7 , we have to create a new column.

new column =

var _max = maxx(filter(Table, Table[Date]>= earlier([Date]) && not(isblank(Table[Code])) ), [Code])

return

if(isblank([code]), _max,[Code])

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8