Power Pivot comes with some very good time intelligence functions, however, to get the most from these functions it’s a best practice to provide Power Pivot with a Date table.
Date tables can be used to report on Calendar, Financial or some other form of dates.
The best way to do this is to talk with your database administrator and ask them to provide a view from SQL Server that you can load into your model. The benefit of this approach is that the single version of the Date Table can be consumed in many different models, furthermore, it’s better to have one table to maintain than several tables.
If you’re unable to source a Date table this way you can create it in Excel and then create a CSV file on the server.
I have created an example Date table at the link below that you can use as the basis for your own version.
Try and steer away from using the Excel linked data facility.