Nested IF Statement with math on column

[gsed99]

Hello,

I want to perform an IF Statement on a column which is formatted in YYYYMM. 

I have been able to do this in Excel, but new to DAX and PowerBI.

Here is my formula in Excel:

=IF(RIGHT(A3,2)="01",LEFT(A3,4)-1&13,A3)

When applied to a value that is 201701, it should become 201613, and for any records not ending in 01, they are left alone.

I am trying to get this to work in DAX with PowerBI....

Which would translate to DAX:

=IF(RIGHT('Marketing Snapshot'[DateStampMonth],2)="01",LEFT('Marketing Snapshot'[DateStampMonth],4)-1&12,'Marketing Snapshot'[DateStampMonth])

Any help will be greatly appreciated!

[Interkoubess]

Hi @gsed99,

Did you use this formula as a calculated column?

Because I have tried it and it worked...

Let us know...

[gsed99]

Hmmm, I get an error saying "Expressions that yield varian data-type cannot be used to define calculated columns."

Can you paste the formula that worked for you?

[gsed99 · Accepted solution]

I was able to get it to work, had to apply a FORMAT expression on the last part:

DateStampMonth New = IF(RIGHT('Marketing Snapshot'[DateStampMonth],2)="01",LEFT('Marketing Snapshot'[DateStampMonth],4)-1&13,FORMAT('Marketing Snapshot'[DateStampMonth],"General Number"))