create a new column based on another column by date

[KMalik]

Hi there, 

I'm new to Power BI, can you please advise on how to create a new column with the following logic:

if [Year] is equal to or greater than [current year] then var2 = [Value] else blank

[year column] is in date year format yyyy.

I know it might be basic but can't make it work somehow. Thanks for your help.

[MNedix · Accepted solution]

Heya,

You are comparing two different data types so it doesn't know what to return. I recreated your model and you have to do one of two things:

1. set Year column as Whole number and Value as Decimal number and use this formula:

Forum = 
VAR _year = YEAR(TODAY())
RETURN
IF(Sheet1[Year]>=_year,Sheet1[Value],0)

2. Set Year column as Whole number and Value as Text and use this formula:

Forum = 
VAR _year = YEAR(TODAY())
RETURN
IF(Sheet1[Year]>=_year,Sheet1[Value],"0")

The second option doesn't really make sense but it also works.

Best,

PS: if this solved your problem then please mark it as the solution so others can see it

[Ritaf1983]

Hi @KMalik 
You can use the formula :

Var2 =

var current_year = YEAR(TODAY())

RETURN

IF([Year]>=current_year,[Value],"")

The pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[KMalik]

I tried it but it gives an error and not sure how to proceed with it. Any idea?

I was testing a bit and my suspicion is that YEAR(TODAY()) is coming out as a whole number. I tried just creating another variable = YEAR(TODAY()) and it comes out as a whole number and when I changed the format to date the values are incorrect, 2024 changes to 1905.

[Ritaf1983 · Accepted solution]

Hi @KMalik 
The year function returns the whole number, you cannot convert it to date format because the engine doesn't know that this is a year, it is just a number for it.

According to the error you are getting, there is issue with the data types.
1. You need the column year to be a whole number not a formatted date.
2 .If the wanted result is a number then it cannot return "" .
The "" is string so the data type should be a text

If you need it as a decimal number with blank cell you can use the formula :

Var2 =

var current_year = YEAR(TODAY())

RETURN

IF([Year]>=current_year,[Value],BLANK())
The updated pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[MNedix · Accepted solution]

Heya,

You are comparing two different data types so it doesn't know what to return. I recreated your model and you have to do one of two things:

1. set Year column as Whole number and Value as Decimal number and use this formula:

Forum = 
VAR _year = YEAR(TODAY())
RETURN
IF(Sheet1[Year]>=_year,Sheet1[Value],0)

2. Set Year column as Whole number and Value as Text and use this formula:

Forum = 
VAR _year = YEAR(TODAY())
RETURN
IF(Sheet1[Year]>=_year,Sheet1[Value],"0")

The second option doesn't really make sense but it also works.

Best,

PS: if this solved your problem then please mark it as the solution so others can see it