Add an additional number to a figure in a column based on the date in another columnn

[Xponti]

Power BI Version 2.74

Hi Everyone, I am working on a DAX If statement to get basically the following formula. I am using it to get a custom column based on the [budget] column.

If the date in Column [date] is equal to 01/06/2020 then add 30,000,000 to the figure in column [budget] otherwise [budget]

I have used the following with no success.

If [date]=01/06/2020

then

([budget]+30000000)

else

[budget]

however for some reason the 'if' statement is not picking up the logical test of [date]=01/06/2020 and then it is not adding 30,000,000 to the budget figure.

I have tested the [date] column by adding an additional column using the Conditional Column function and using the 'if' function to put the date in the cell that I am attempting to change and that works.

Would appreciate any assistance.

[date] column set to Date type

[budget] column set to Fixed Decimal Number type

[Xponti · Accepted solution]

All Thanks for providing answers for me to consider. Through a bit more investigation I found a work around solution to this.

1. First off i found that my Date column was expressed at a text from the original import to Power BI and this was causing issues.

2. I added a conditional column using Date equals 1/06/2020 then 30000000, elseif date equals 01/06/2022 then 70000000 else [Budget]

3. I added another Conditional Column the same as above but replaced the 30000000 with a 1 and the 70000000 with a 2 and the else was 0

I then added another custom column that added the Budget column to the the first conditional column when the figure in the second conditional column was either a 1 or a 2 else populate with the figure in the [Budget] column. This got me the result I was working to get.

[mahoneypat]

Looking at the posts, it looks like you used [date] in your first if but in the later pic your column was [Date].  M/Query Editor is case sensitive, so that may explain why it didn't work.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Xponti]

Simple error on my part when writing the original post. If you look at the available columns in the screenshot you can see that the available Column is in fact [Date] with a capital.  This is used in the formula so the case sensitivity should not be the issue in this case. When I write the formula I am using the "insert" to insert the reference to the column listed as well to ensure that there is no error with referencing.

The "DATE" in the error I am assuming refers to the "DATE(2020, 6, 1)" function.  It is not recognising the function DATE in this particular part of the IF statement, not the [Date] reference to the column.

Thanks for taking the time to comment. Appreciate your input.

[Mariusz]

Hi @Xponti 

Try this.

Measure = 
IF( 
    'Table'[date] = DATE( 2020, 6, 1 ),
    'Table'[budget] + 30000000,
    'Table'[budget]
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Xponti]

This is how I entered the formula and i get an error

Expression Error: The name "DATE" wasn't recognised. Make sure it is spelled correctly

Thanks for answering though.