date(year(), month(), 1) doens't work in PowerPivot?

[CelineT]

Hello,

I have 2 columns with dates that need to be adjusted to 1st of the month if day<15, or next month if day >=15

If I use the date( year(), month() + if(day<15, 0, 1), 1) in excel, it works fine, but in powerpivot it gives an error.


When I break it down in steps:
- date(year(), 1, 1) works fine
- date(year(), month(), 1) doens't work anymore
It seems that PowerPivot blocks as soon as 2 items are made variable.

Anyone have a solution?

Thanks
Céline

 

[James006]

Hello Céline,

My guess would be that you do need to specify ...

=date(year(serialnumber), month(serialnumber), 1)

HTH

 

[CelineT]

sorry, tried to be short, between brackets I referred to a column "start date" or "end date".

so the full formula in excel, which works fine, is:
=date( year(start date), month(start date) + if(day(start date)<15, 0, 1), 1)

the "start date" is identified as a date, so I presume Excel stores it as a serial number so that would be fine?

 

[James006]

Yes you are right ...

Should you need to make sure your dates are actual serial numbers ...

use the keys Control+1 and select general ...

Hope this will help

 

[RoryA]

This works fine for me in Power Pivot with a date Field1:

=date(year([Field1]),month([Field1])+if(day([Field1])<15,0,1),1)

 

[CelineT]

oh great, I have found my mistake! It wasn't in the formula itself but I had some blanks in the start or end date columns which gave the error. When I added if(isblanck(date); ... ; ...) it worked.

Thanks for taking the time to reply!