4 Replies Latest reply on Feb 14, 2019 12:36 AM by Norbert Maijoor

    Dynamic Fiscal Year Start

    Eoghan Lyons

      Hi all,

       

      I have a list of companies, most of which have a fiscal year start in February. There are however a couple of companies which have a fiscal year start in January.

       

      I would like to have it so that if I select a company, that the dates shown automatically show the FY for the right dates. Is this possible?

       

      I'm aware of a method which adds months to the dates, but this has a negative knock on effect of displaying a date different to what date (month, week, date etc) it should be, so this isn't what I'm looking for.

       

      Any ideas?

       

      Many thanks,

      Eoghan

        • 1. Re: Dynamic Fiscal Year Start
          Patrick A Van Der Hyde

          Hello Eoghan,

           

          You can set Fiscal Year start dates by data source.  Is it possible to duplicate your data source and make one for the different start date?  Then bring them together in a common report with Blending or maybe just two different views on a comman dashboard? 

           

          Patrick

          • 2. Re: Dynamic Fiscal Year Start
            Eoghan Lyons

            Hi Patrick,

             

            Thanks for that, this looks to have a degree of promise but it seems to fall down around the fact that the formats are dates.

             

            I've attempted a formula where:

             

            IF SUM([SourceCopy.[Count])>0 then ATTR([SourceCopy.[Date]) else attr([Date]) END

             

            Basically if the brand has a specific value (I can't just use a if brand="X" formula as that creates a mixed aggregate problem)  then use the dupe date source, otherwise use the original data source. The formula gives me a result but it's stored as a measure by default.

            • 3. Re: Dynamic Fiscal Year Start
              Raoul Verhaegen

              Hi Eoghan,

              What kind of viz are you looking to build exactly?

              I had the same question and my viz is a table with figures; each row is a year and each column is a month.

               

              I wanted to be able to switch financial year start in my dashboard through a parameter. This was achieved quite simply with 2 calculated fields:

              - One that sets the sort order for months in my table (July to June for some businesses, Jan to Dec for others)

              - One that re-arranges which month counts towards which year (July to December 2018 count towards 2019, etc.)

               

              Let me know if you want more details.

               

              Cheers,

               

              Raoul

              • 4. Re: Dynamic Fiscal Year Start
                Norbert Maijoor

                Hi Raoul,

                 

                Not sure if it is applicable in your scenarion but find my approach as alternative approach below and stored in attached workbook version 10.5 located in the original thread.

                 

                 

                1. Date (Month/Year)

                 

                 

                2. FY:

                if [Date (Month / Year)]>=201802 and [Date (Month / Year)]<=201901 and [Company]='A' then 'FY2018'

                elseif [Date (Month / Year)]>=201902 and [Date (Month / Year)]<=202001 and [Company]='A' then 'FY2019'

                elseif [Date (Month / Year)]>=202002 and [Date (Month / Year)]<=202101 and [Company]='A' then 'FY2020'

                elseif [Date (Month / Year)]>=201801 and [Date (Month / Year)]<=201812 and [Company]='B' then 'FY2018'

                elseif [Date (Month / Year)]>=201901 and [Date (Month / Year)]<=201912 and [Company]='B' then 'FY2019'

                elseif [Date (Month / Year)]>=202001 and [Date (Month / Year)]<=202012 and [Company]='B' then 'FY2020'

                END

                 

                Hope it helps,

                 

                Regards,

                Norbert