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:

Sample Data for Data Fill Down 

Thanks in advance. 

 

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 )

14 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    rush Try this column

    Column 2 = CALCULATE(MAX('Table'[Status]),ALLEXCEPT('Table','Table'[ID]))
     
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

      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.

  • 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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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

      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.