Create a column based on criteria in other two columns in the data set

[key_to]

Hello,

I have a table with employees and their yearly salary over 4 years. In this table I need a calculated column which would show the latest salary amount based on a date in another column in the same row. If the latest amount is zero or empty cell, it needs to find the next most recent amount and use this number instead.

So basically something like: if (and (current amount=0,date = latest date), find previous amount, current amount)

There are only four dates in the data set. 31.10.2019, 31.10.2020, etc.., so basically 4 rows per person for those who have been in the company for all four years. 

This is an example of the dataset: 

Does anyone have experience with something like this?

Appreciate any help!

Thanks in advance! 

[Uspace87]

@key_to 

I have changed the formula, can you try this:

Earlies_Salary =

 VAR Earlies_Date =

CALCULATE(

    MIN(Salaries[Date]),

  FILTER(ALL(Salaries),

  Salaries[Employee]=EARLIER(Salaries[Employee]) && Salaries[Salary]<>BLANK()

  )

)

Return

CALCULATE(

    SUM(Salaries[Salary]),

    FILTER(Salaries,

    Salaries[Date]=Earlies_Date

    ))

[key_to]

Thanks very much @Uspace87!

I tried your formula, but I am probably doing something wrong. I get the following error message: 

Could you please see what the problem is? And will this formula actually give me a new column? Or is it just a measure? Thanks again! 

[Uspace87]

Hi,

I have used to a calculated column. Are you creating a measure? That could be the reason why you get the error.

[key_to · Accepted solution]

@MAwwad 

@Uspace87  Just to let you know -After trial and error I found a solution for my case. I added the column in DAX using the following formula:

 VAR currentRowDate = 'PBI Compensation'[Date]

VAR currentSalary = 'PBI Compensation'[BasePay in NOK]

VAR previousSalary = LOOKUPVALUE('PBI Compensation'[BasePay in NOK], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[SuccessFactor ID], 'PBI Compensation'[Date], CALCULATE(MAX('PBI Compensation'[Date]), FILTER('PBI Compensation', 'PBI Compensation'[Date] < currentRowDate)))

RETURN IF(ISBLANK(currentSalary), previousSalary, currentSalary)

[key_to]

@Uspace87  Unfortunately the problem must be somewhere else, I am trying to get a new column just like you.

[Uspace87]

@key_to 

EarliestSalary = CALCULATE( MIN('Employee'[Salary]), FILTER( ALL('Employee'), 'Employee'[EmployeeID] = EARLIER('Employee'[EmployeeID]) && 'Employee'[Salary] <> BLANK() ) )

[MAwwad]

Yes, you can create a calculated column in Power BI to achieve this. Here's an example of the DAX formula you could use:

Latest Salary = VAR current_date = [Date] VAR current_amount = [Amount] VAR latest_salary = FILTER( ALL(Employee), Employee[Name] = EARLIER(Employee[Name]) && Employee[Date] <= current_date && NOT(ISBLANK(Employee[Amount])) ) RETURN IF( ISBLANK(current_amount) || current_amount = 0, MAXX(latest_salary, Employee[Amount]), current_amount )

In this formula, the VAR statements define variables that hold the current date and salary amount for the employee being evaluated. The FILTER function then searches the entire Employee table for rows that match the employee's name and have a date that is less than or equal to the current date and a non-empty salary amount. The MAXX function then returns the highest salary amount from the filtered rows, which is the latest non-zero salary. Finally, the IF statement checks if the current salary amount is blank or zero and returns the latest non-zero salary if it is, or the current salary amount if it isn't.

You can replace "Name" and "Date" with the appropriate column names in your dataset, and "Amount" with the name of your salary column.

[key_to]

@MAwwad Thanks for this! I have similar measure the the one you are giving me. That works fine in DAX. The problem is that this only gives me the measure and not value in each row which I am after. My formula that works looks like this. 

adjusted salary col =

VAR previousDate =

    CALCULATE (

        MAX ( 'PBI Compensation'[Date] ),

        FILTER ( ALL ('PBI Compensation'),'PBI Compensation'[BasePay in NOK] > 0 && 'PBI Compensation'[Date] < MAX ( 'PBI Compensation'[Date] ) )

    )

VAR previousSalary =

    CALCULATE (

        SUM ( 'PBI Compensation'[BasePay in NOK] ),

        FILTER ( ALL ( 'PBI Compensation'), 'PBI Compensation'[Date]= previousDate)

    )

    VAR currentSalary =

    CALCULATE (

        SUM ( 'PBI Compensation'[BasePay in NOK] ),

        FILTER ( ALL ( 'PBI Compensation'), 'PBI Compensation'[Date]= MAX ('PBI Compensation'[Date])

    )

) RETURN  If (currentSalary=0,previousSalary,currentSalary)

Maybe you could help me to change this formula so that it actually adds column into my data set?

Thanks! 

[MAwwad]

Sure, I can help you modify your DAX formula to add a new column into your dataset instead of just returning a measure.

To do this, you can use the DAX function ADDCOLUMNS. This function allows you to create a new table by adding new columns to an existing table. Here's an example of how you can modify your formula to add a new column called "Adjusted Salary" to your 'PBI Compensation' table:

Adjusted Salary = ADDCOLUMNS ( 'PBI Compensation', "PreviousDate", CALCULATE ( MAX ( 'PBI Compensation'[Date] ), FILTER ( ALL ( 'PBI Compensation' ), 'PBI Compensation'[BasePay in NOK] > 0 && 'PBI Compensation'[Date] < MAX ( 'PBI Compensation'[Date] ) ) ), "PreviousSalary", CALCULATE ( SUM ( 'PBI Compensation'[BasePay in NOK] ), FILTER ( ALL ( 'PBI Compensation' ), 'PBI Compensation'[Date] = [PreviousDate] ) ), "CurrentSalary", CALCULATE ( SUM ( 'PBI Compensation'[BasePay in NOK] ), FILTER ( ALL ( 'PBI Compensation' ), 'PBI Compensation'[Date] = MAX ( 'PBI Compensation'[Date] ) ) ), "Adjusted Salary", IF ( [CurrentSalary] = 0, [PreviousSalary], [CurrentSalary] ) )

This formula creates a new table by adding four new columns: "PreviousDate", "PreviousSalary", "CurrentSalary", and "Adjusted Salary" to the 'PBI Compensation' table. The "Adjusted Salary" column is calculated using the same logic as your original measure.

Note that the "PreviousDate", "PreviousSalary", and "CurrentSalary" columns are intermediate columns that are used to calculate the final "Adjusted Salary" column. You can remove these intermediate columns from the formula if you don't need them in your final result.

I hope this helps!

[key_to]

Thanks for all your time @MAwwad! This solution is exactly would I am looking for! However when I apply the formula I get an error: 

Do you have a suggestion how to solve this? Thanks again!

[serpiva64]

Hi,

please post some sample data