Forum Discussion
Sales Analysis with Custom Calendar
Hi Everybody:
I was doing this Sales report for the company i work for, and I was advice with doing the same report but to consider Fiscal Years instead of Regular Year. The first one starts on March and ends on February.
So my questions is how can I build the same report but having the results considering a year starting in March and ending up in February?
Kind regards.
Anonymous,
If I'm understanding correctly, just add additional columns to your 'Calendar' table where you redefine the [MonthNum]. Then you'll have a normal calendar hierarchy and a fiscal hierarchy to place in your visual.
There are many ways to accomplish this either via DAX SWITCH ( ) or in Power Query Editior.
A pretty good article on SWITCH ( ) is found at https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/. The Power Query Editor column could be an IF ( ) or you can use this article https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/.
2 Replies
Anonymous,
If I'm understanding correctly, just add additional columns to your 'Calendar' table where you redefine the [MonthNum]. Then you'll have a normal calendar hierarchy and a fiscal hierarchy to place in your visual.
There are many ways to accomplish this either via DAX SWITCH ( ) or in Power Query Editior.
A pretty good article on SWITCH ( ) is found at https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/. The Power Query Editor column could be an IF ( ) or you can use this article https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/.
Awesome Function!
That was what I've been looking for.
Gracias compadre ChrisMendoza