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 )
tamerj1's avatar
tamerj1
Community Champion
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 )
amrhitch's avatar
amrhitch
New Member
3 years ago

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's avatar
    tamerj1
    Community Champion
    3 years ago

    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.