Custom column to retain old cost for previous years and add new cost to the year 2022 and forward

[BBIUser]

Hello,

There is an existing Power BI Query which is used\source for a Power BI report.

Custom Column 

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * 95

95 is the old cost. the new cost will be 100. The formula needs to reflect to show old cost for previous years (2021 - 2018) and add new cost to the year 2022 and forward. I am not into coding. So, can any coding experts show me the formula to retain the old cost and add new cost for current and forward years?

Appreciate your help!

[jbwtp]

Hi @BBIUser,

Please try if this works for you:

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if Date.Year([Date]) > 2021 then 100 else 95)

Cheers,

John

[BBIUser]

Thanks for the reply John @jbwtp . Apprecaite it!

How does the formula change if I have to consider date and month? say for example, 

if [Session Admin]= "Not assigned Session" then 0 else [Quantity] * (if [Date] > 6/30/2021 then 100 else 95).

It is throwing Error if I use [Date] > 6/30/2021.

I want to actually use the 'Date' column from the screenshot.

Also, not sure if I need to enter the 12:00:00 AM time that is displayed as part of the 'Date' column.

Thanks in advance!

[jbwtp · Accepted solution]

Hi @BBIUser 

If you want to use dates as comparison, it needs to be in a proper format which PQ would understand. It can be achived using the #date funciton like #date(2023,1,1). If you wan tto add time as well, it would be the #datetime() function, used like #datetime(2023,1,1, 15, 30, 0).

You will need to make sure that the other operand (the [Date] in your post above) is also the same type: date when compating to #date() and datetime when comparing to #datetime(). This can be achived by using Date.From() or DateTime.From() respectively.

Cheers,

John