Forum Discussion

rush's avatar
rush
Helper V
4 years ago
Solved

Fill down values with DAX calculated column

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...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )
PC2790's avatar
PC2790
Community Champion
4 years ago

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's avatar
    rush
    Helper V
    4 years ago

    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.