Power BI DAX syntax to calculate value from two different column for different time frame

[Kavya_Mahesh]

Hi,

I have three type of value Low, Average and High calculated in three different column for a 1000+ entries in excel for timeframe starting from 1970 to 2030.

What would be the DAX syntax to create new column and get high value for year 1970 to 2026 and Avergae value for year 2027 to 2030.

Appreciate help on this.

[govind_021]

Hey Try this approach , 
New Value =
IF(
YEAR(YourTable[DateColumn]) <= 2026,
YourTable[High],
IF(YEAR(YourTable[DateColumn]) >= 2027 && YEAR(YourTable[DateColumn]) <= 2030,
YourTable[Average],
BLANK() -- This will return blank for other years outside 1970-2030
)
)

[v-qiaqi-msftv]

Hi @Kavya_Mahesh，

Thanks for the reply from FarhanJeelani.

Could you please share a bit more about your excel table like sample data?

Do you want to create a calculated column assigning different value based on year addressed?

If so, I have made a test for your reference.

Here is my sample data:

You could directly create a calculated column and write the DAX as below:

NewColumnTest = 
VAR _YearValue = YEAR('Table'[Date])  -- Extract the year from DateValue
RETURN
    IF(
        _YearValue <= 2026, 
        'Table'[High], 
        IF(
            _YearValue >= 2027 && _YearValue <= 2030, 
            'Table'[Average], 
            BLANK()
        )
    )

Here is the testing result:

I would be grateful if you could provide me with the pbix file or sample data.

The pbix file is attached.

If you have any other question please feel free to contact me.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Kavya_Mahesh]

Hi,

Thank you so much for the solution.

The sample you have created is exactly what I was looking for, except I directly have year column, so I guess I can skip the extract function.

Regards

Mahesh 

[FarhanJeelani · Accepted solution]

Hi @Kavya_Mahesh ,

Try this

NewColumn =
IF(
'YourTable'[Year] >= 1970 && 'YourTable'[Year] <= 2026,
'YourTable'[High],
IF(
'YourTable'[Year] >= 2027 && 'YourTable'[Year] <= 2030,
'YourTable'[Average],
BLANK()
)
)

[Kavya_Mahesh]

Hi,

Thank you so much.

This worked perfectly.

Regards

Mahesh