Create a column that returns value of another column while keeping the original value if no matches

[ESG]

Hi all,

I'm completely new to PowerBI and have been exploring posts on the community board, but I can't seem to find a solution to my query and I really need help.

I have a table with 3 companies ranging across year 2022 and year 2023.

I am trying to insert a new column called 'No. of customers at investment', which is actually the number of customers at start of year for 2022:

How do I create a column that would return the value of 'No. of customers at start of year' for year 2022, regardless of whether the 'Year' column is 2022 or 2023?

Many thanks in advance!  

[ESG]

thank you very much @Jihwan_Kim and @Anonymous for your guidance! 

[Anonymous · Accepted solution]

Hi @ESG ,

Your solution is great, @Jihwan_Kim . It works like charm! Here, I have another idea, I want to share it for reference. I used the calculation column instead of Measure.

Investment =
CALCULATE (Max ('Table' [Start]), Filter (all ('table'), 'table' [year] = 2022 && 'table' [company name] = Earlier ('table' [company name]))

The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

# customers at investment: =
CALCULATE ( SUM ( data[#_customers_start_of_year] ), 'year'[year] = 2022 )