Sorting Months Fiscally

[Safety Guy]

I am having trouble getting the months in a PowerPivot table to arrange chronologically by fiscal year, not calendar year. I...I mean...The Powers That Be (TPTB) want data by fiscal year, which starts on April 1st. No, that isn't a joke and TPTB didn't appreciate my humorous (to me) comments.

TPTB want a table with fiscal years as columns (no problem) and month names as rows starting with April as the first month (problem) with monthly totals and cumulative monthly totals as data. And that's where things go sideways. I can create a pivot table and manually re-arrange the months, but the cumulative total is calculating from January. Therefore April's cumulative total isn't just April, it is January - April.

If I use fiscal quarters above the months, everything is peachy. However, TPTB do not want to see quarters. Just months. I can get the data to do what TPTB want, I just can't get the months to arrange correctly and it is driving me bat-crap crazy! I'm about to lose my cherub-like demeanor...


Thanks for any help in advance,
Safety Guy

 

[akice]

You don't give any information about tables, model design, relationships, etc. but I think you need to look at the function reference for various date related functions. Many have a year end date parameter for non calendar based fiscal years.

https://msdn.microsoft.com/en-us/library/ee634396.aspx

 

[Safety Guy]

Apparently, I needed to put the months in fiscal order manually THEN add my measures, not the other way around. Like I was doing. Crisis averted. Thanks!

Safety Guy

 

[citizenbh]

Maybe, TOTALYTD