Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

Fiscal Year - April to March

Hi,   I've been stuck on a project for a while and I've had a problem which I thought was simple but I'm having some problems with. I'm working with data from 2015 and I want to show the fiscal yea...
Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    7 years ago

    A column with this cal should allow you to sort perfectly across year and month ranges. The only problem is that the date column of this table will not sort on this. You might have to create a month-Year Col for display

     

    SortCol = CONCATENATE(if([Date].[MonthNo]> 3,DateTable[Date].[Year],DateTable[Date].[Year]-1) ,format(if(DateTable[Date].[MonthNo]> 3,DateTable[Date].[MonthNo]-3,DateTable[Date].[MonthNo]+9),"00"))

     

     

    Please download the file again from dropbox, which I uploaded. I have corrected fiscal Year cal, created month-year and sort col. Changed the graph and the year filter.

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
7 years ago

Ahhh, I see what you've done. Good stuff. Getting closer

 

Do you know how I would go about sorting the months so it's April to March instead of January to December? 

Joc008's avatar
Joc008
Frequent Visitor
7 years ago

As suggested above - use column "Yr-Mo" which is there on purpose (because it allows sorting). If you created new column with "mmm" notation, make this column sorteable by column "Yr-Mo".

 

Select this new column: Modelling, Sort by Column, select Yr-Mo. Voila.

  • michael_knight's avatar
    michael_knight
    Icon for Post Prodigy rankPost Prodigy
    7 years ago

    Sorry fella's, struggling to follow. Are you saying I need to create a new column in the Date Field with the Year and Fiscal month merged in it? 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    7 years ago

    A column with this cal should allow you to sort perfectly across year and month ranges. The only problem is that the date column of this table will not sort on this. You might have to create a month-Year Col for display

     

    SortCol = CONCATENATE(if([Date].[MonthNo]> 3,DateTable[Date].[Year],DateTable[Date].[Year]-1) ,format(if(DateTable[Date].[MonthNo]> 3,DateTable[Date].[MonthNo]-3,DateTable[Date].[MonthNo]+9),"00"))

     

     

    Please download the file again from dropbox, which I uploaded. I have corrected fiscal Year cal, created month-year and sort col. Changed the graph and the year filter.