OR Formula in Power Pivot

[Deep Dave]

I am trying to input a Formula in PowerPivot as below -

=IF(OR([Month]=1,[Month]=2,[Month]=3),YEAR([Excise_Invoice_Date]-1),YEAR([Excise_Invoice_Date]))

I However realized OR can handle only 2 Arguments in Power Pivot.

I simply want the Year to be returned as the Actual Year - 1, if the Month serial number is 1,2 or 3.

However, the Formula is returning an Error.

Can anyone help?

Deep

 

[Norah]

I assume that you're creating a calculated column, so this should work:

IF(
    [Month]=1 || [Month]=2 || [Month]=3, 
    YEAR([Excise_Invoice_Date]) - 1, 
    YEAR([Excise_Invoice_Date])
)

 

[Deep Dave]

Works Perfectly!

Just wanted to understand, what does || denote?

Thank You,

Deep

 

[Norah]

The "||" means OR; it just doesn't have the two-item limit that the OR() function has. You can also use "&&" for AND. Once you start using || and &&, you'll probably find that you never bother with OR() or AND() in your Power Pivot models because they're just so much more convenient. (At least it's been that way for me!)

 

[Deep Dave]

Hi!

Thank you for the reply. It helped a lot.

There is 1 more Question I am seeking an answer to if you could help -

http://www.mrexcel.com/forum/powerpivot-questions/765159-share-business-calculation.html