Forum Discussion

NehaVageriya's avatar
NehaVageriya
Advocate II
9 years ago
Solved

fiscal year flag

Hi, 

 

I have a date dimension which has all Fiscal calculated columns. Like Fiscal Year, Fiscal Year Month Number and week as well. I am looking to create a flag for CurrentFiscalYear and PreviousFiscalYear. I have tried it to be as calculatedcolumn, but I am stuck with just how to go about it, I have tried a few things but no good logical solution I could come up with. 

Need Help

7 Replies

  • NehaVageriya's avatar
    NehaVageriya
    Advocate II
    9 years ago

    The moto here is filter slicer and data on report/dashboard by CurrentFiscalYear (so looking for a boolean here )

    • Mi2n's avatar
      Mi2n
      Microsoft Employee
      9 years ago

      You would need a mechanism to figure out the current fiscal year first. So for example we are currently in Fiscal year 17 (Jan 2017). So, in order to figure that out, we will use,

       

      IF(MONTH(TODAY()) <= 6, YEAR(TODAY()), YEAR(TODAY()) + 1)

      This will give the current fiscal year (2017). [Note that I have considered that the fiscal year starts at July]

       

      Now, in the calculated column just use this formula to compare against the Fiscal year field that you already have.

       

      IF('Calendar'[Fiscal_year] = <The above formula>, 1, 0)

      The beauty here is that the 1st formula can be created as a calculated measure and it can instead be used in the second formula.

      • NehaVageriya's avatar
        NehaVageriya
        Advocate II
        9 years ago

        Thanks for this it looks very simple. Just a quick question should the Year(Today()) be + 1 or -1? I think should be -1. As if month is <=6 I would consider previous calendar years months in this fiscall year. 

         

        But rest looks good. Thanks, again 

    • parry2k's avatar
      parry2k
      Super User
      9 years ago

      What is start date for your fiscal year? I have calendar and our fiscal year starts on Aug 01st

       

      Thanks,

      P