create a new column based on another column by date

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.

Need Help

MNedix
1 year ago
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
1 year ago
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.

columnYear.pbix18 KB

4 Replies

Ritaf1983
Super User
1 year ago
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.

columnYear.pbix18 KB
- KMalik
  Frequent Visitor
  1 year ago
  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
    Super User
    1 year ago
    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.
    
    columnYear.pbix18 KB