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 Previous...
Need Help
  • parry2k's avatar
    parry2k
    9 years ago

    NehaVageriya is this resolved or need further help?

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 

    • Mi2n's avatar
      Mi2n
      Microsoft Employee
      9 years ago

      The months greater than 6 (june) will belong to the next financial year, not the previous financial year. So, it would be +1.

    • parry2k's avatar
      parry2k
      Super User
      9 years ago

      NehaVageriya is this resolved or need further help?