5 4 4 Fiscal Calendar-New Power Pivot User

[heidiatmn]

I have googled for hours, and searched this forum-I'm very new to power pivot and seen how powerful of a tool it could be for us, but before I can wow anyone with even a simple report (and get them to give me access to what I want) I need to get over this hurdle. Our fiscal calander is 5 week 4 weeks 4 weeks. I see all these amazing DAX calculations but I haven't found one that I think would apply as I need to report by fiscal years/fiscal months/and sometimes even fiscal weeks. Is my best route to do another tab to look up dates and return the correct fiscal month/week/yr information?

 

[pete234]

I have googled for hours, and searched this forum-I'm very new to power pivot and seen how powerful of a tool it could be for us, but before I can wow anyone with even a simple report (and get them to give me access to what I want) I need to get over this hurdle. Our fiscal calander is 5 week 4 weeks 4 weeks. I see all these amazing DAX calculations but I haven't found one that I think would apply as I need to report by fiscal years/fiscal months/and sometimes even fiscal weeks. Is my best route to do another tab to look up dates and return the correct fiscal month/week/yr information?

If your source is an SQL server, you might have DimDate table of your fiscal periods which would allow you to group and report on your custom calendar.
Otherwise, you can try to find the DimDate powerpivot workbook which allows you to program your own periods, export/link and import it to Powerpivot to be in your relationships.
I don't have link handy for it but I used it to build out my 4-4-5 calendar.
However, as I recently found out it will not allow you to use any of the built in DAX that compare quarters or periods as they go by regular calendar, really no different than the limits in regular Excels functions.

 

[marco.russo]

I have a draft of an article I'm working on to produce exactly the common calculations (YTD, YOY and so on) using 544 or 445 calendars. Long story short, you have to rewrite the logic of Time Intelligence functions by using just CALCULATE and FILTER. I will update you as soon as I publish it - but according to my schedule it could require a few week. Let me know if you have some specific question, maybe I can provide you some excerpt of the DAX formulas required.

 

[Laurent C]

To be able to "report by fiscal weeks, fiscal months", ... you will need to include this information in your date table. Calculated columns might work but the simplest solution is probably to fill the table as required.

 

[heidiatmn]

Thanks for all the replies-it appears I will have to get IT on board before I can get anywhere near this subject-seems to be more of a battle than I thought it would be.

 

[powerpivotpro]

Try this series of articles called The Greatest Formula in the World:

The Greatest Formula in the World, Part 3 « PowerPivotPro

As well as this helper post for creating the date table:

Excel 5-Calendar Date Table « PowerPivotPro