Forum Discussion
rush
Helper V
Helper VFill 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...
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 )
rushHelper V
Helper Vtamerj1 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
Community Champion
Community ChampionPlease 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 )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
