Fill down values with DAX calculated column

[rush]

Hi All

I need help trying to fill values down based on the date I have for each staff.

Please see the example below with the expected column to be created along with the link to the sample data:

Sample Data for Data Fill Down 

Thanks in advance. 

[tamerj1]

@rush 

Just wanted to add that my code retrieves the last available value before the blank incase you have multiple available values per Id with blanks inbetween the values. 

[rush]

@tamerj1 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.

[tamerj1]

@rush 

This is exactly what it does

[Tahreem24]

@rush Easy solution is below:

[Tahreem24]

@rush Try this column

Column 2 = CALCULATE(MAX('Table'[Status]),ALLEXCEPT('Table','Table'[ID]))

[PC2790]

Hey @rush ,

Here, try this:

New Value = 
VAR LastNonBlankID =
    CALCULATE (
        LASTNONBLANK ( FillDown[ID], 1 ),
        FILTER (
            ALL ( FillDown),
            FillDown[ID] <= EARLIER ( FillDown[ID])
                && NOT ( ISBLANK ( FillDown[Employment Status] ) )
        )
    )
RETURN
    CALCULATE (
        Max ( FillDown[Employment Status] ),
        FILTER ( ALL ( FillDown ), FillDown[ID] = LastNonBlankID )
    )

Outcome:

[rush]

@PC2790 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.

[tamerj1]

Hi @rush 

you can use

Employment Status New =
VAR CurrentStatus = TableName[Employment Status]
VAR CurrentIdTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR NoBlanksTable =
    FILTER ( CurrentIdTable, TableName[Employment Status] <> BLANK () )
VAR LastDateWithData =
    MAXX ( NoBlanksTable, TableName[Date] )
VAR LastStatus =
    MAXX (
        FILTER ( NoBlanksTable, TableName[Date] = LastDateWithData ),
        TableName[Employment Status]
    )
RETURN
    IF ( ISBLANK ( CurrentStatus ), LastStatus, CurrentStatus )

[rush]

@tamerj1 Thank you but I forgot to mention that staff can have multiple employment status which needs to fill in until the next one if there is. Currently, it does not do that.

[tamerj1 · Accepted solution]

@rush 

Please try

Employment Status New =
VAR CurrentDate = TableName[Date]
VAR CurrentStatus = TableName[Employment Status]
VAR CurrentIdTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR NoBlanksTable =
    FILTER ( CurrentIdTable, TableName[Employment Status] <> BLANK () )
VAR NoBlanksTableBefore =
    FILTER ( NoBlanksTable, TableName[Date] < CurrentDate )
VAR LastDateWithData =
    MAXX ( NoBlanksTableBefore, TableName[Date] )
VAR LastStatus =
    MAXX (
        FILTER ( NoBlanksTableBefore, TableName[Date] = LastDateWithData ),
        TableName[Employment Status]
    )
RETURN
    IF ( ISBLANK ( CurrentStatus ), LastStatus, CurrentStatus )

[amrhitch]

Hi Tamerj, 

I tried your solution and it was working well all over the table but for some reason when the before and after status 'is_available' is ZERO, the calculated column doesn't work

[tamerj1]

@amrhitch 

I had an extensive look at it and still not sure if the issue is a result of the engine confusion between 0 and blank. It seems to me that you need to addd the [MAX] and [MIN] columns to the ALLEXCEPT arguments. Please do that and let me know if it works. 

[rush]

Thank you very much. @tamerj1  It works really well. 🙏

[tamerj1]

@rush 

Ok I guess you are right. Let me try to fix it