Powerpivot: Convert Datetime to Weekly format (mm/dd - mm/dd)

On Excel 2013 here.

1. I imported data from SQL into PowerPivot.
2. I have a datetime column, i.e.: "7/31/2013 2:03:51 PM"
3. I want to create a calculated column which converts that datetime to "of week" in mm/dd - mm/dd format, example for the above datetime: "7/28 - 8/3" as 7/31 fell within that week.

I don't know if there's a single formula to get this, I'm creating multiple calculated columns to get to the final result. I was able to create two new calculated datetime columns with the first day of that week, and the last day.
So for the above datetime I have 2 new datetime columns:
FirstDayofTheWeek: "7/38/2013 2:03:51 PM"
LastDayofTheWeek: "8/3/2013 2:03:51 PM"

The next step would be to use =MONTH and =DAY to extract the data to get a final column with nice "mm/dd - mm/dd" format right?

The problem:
If I try to execute a =MONTH or =DAY function on a calculated datetime column, PowerPivot errors:
"Calculation error in column 'Query 1'[]: An arument of function 'MONTH' has the wrong data type or the result is too large or too small."

The function I'm running to test this is a simple: =MONTH([FirstDayofTheWeek])

1. The FirstDayofTheWeek column is data type "date"
2. Same thing happens with the =DAY function
3. This works perfectly fine in Excel, but seems broken in PowerPivot
4. This also works fine on the original datetime column that was imported from SQL

Any ideas? Is there a completely easier way of doing this?

Hi,

I have no experience at all with PowerPivot, so I can't help on that front. But I am going to offer you a simpler solution in Excel, based on the fact that you said

I'm creating multiple calculated columns to get to the final result.

This can be achieved with a single cell formula. Assuming your datetime is in A2, use this formula:

Formula:

Please Login or Register  to view this content.

Hope this helps somewhat, even if it doesn't answer the question directly

That works perfectly in Excel but fails in Powerpivot.

In Powerpivot, this works fine (assuming again that A2 has the datetime):
=MONTH(A2)
But this fails with the data type error above:
=MONTH(A2-1)

=MONTH or =DAY will fail if I do any operation on the datetime cell that I'm feeding them.

Originally Posted by Debraj Roy

Are you searching for any DAX query..
Please confirm..

Not entirely sure. I'm importing the SQL data using a SQL Query (I'm not dumping the entire tables from the SQL server).

Thank you for your example - it does work.

I set up a new SQL Server Express 2012 on my home PC, created a table with a few datetime rows, imported into PowerPivot and it does work.

So there's something wrong with the data I imported originally from the SQL server at work, not sure what though. I think I'll start fresh when I get into work and re-import everything.