Forum Discussion
Nested IF Statement with math on column
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!
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"))
3 Replies
Hi gsed99,
Did you use this formula as a calculated column?
Because I have tried it and it worked...
Let us know...
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?
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"))
