2 Replies Latest reply on Apr 10, 2017 11:05 AM by Jim Dehner

    Creation Custom CY And PY.

    Harsha R

      Here I have 3 years Of Emp data. I need to see CY Emp Count & PY Emp Count.  How we can create parameters On Month selection. Month Selection Should be "MM" "YYYY" EX: (Mar 2017).

      My Financial Year Starts Jun-May.

      I need create Custom CY as Jun 2016 to May 2017, (i need to my Emp Count Those Months)

      PY Jun 2015 to May 2016.

      I create Cal Field For CY: If DATEDIFF('year',[Date of Join],today())=0 then [Number of Records] END

      (It's showing CY Jan-Mar data only) My CY Jun 2016 to May 2017.

                                         PY : if datediff('year',[Date of Join],today())=1 and DATEDIFF('year',[Date of Join],today())=1 then [Number of Records] END.


      I think We can Create Custom CY by Using Dateadd 'Month' -5 today()-5. however I'm not sure. Please Help me on this. How we can Create Custom CY, & PY.

        • 1. Re: Creation Custom CY And PY.
          Galen Busch

          Hi Harsha,


          Right click on your date in your dimensions column, scroll down to 'Default Properties' and the bottom of the following menu shows 'Fiscal year start' where you can select your month of FY start.



          • 2. Re: Creation Custom CY And PY.
            Jim Dehner



            Understand what you are trying to do - the issue with using the Fiscal Year feature is that it works fine until you start doing calculated fields using the dates - they revert back to calendar year - you will need to create a calculated field for your Fiscal Year something like > If Month([date of join]) >+6 then Year([Date of join]) else Year([date]) -1 End  - note the value is an integer


            Now you have a field in your table for Fiscal year - don't know what you are doing for Month - if June in now the month 1 then you would do a similar calculated field for Fiscal Month and adjust it by 6 months -  Day is still day -


            Now you can make a date using Makedate - Fiscal Year date >  MakeDate([Fiscal Year], [Fiscal Month], Day(Str([Date of join]))) and you have a Fiscal year in your date format that can be used tables and charts -- in calculations you have to convert Today() into the proper fiscal year as above


            Let me know it that helps