fiscal year flag

[NehaVageriya]

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. 

[NehaVageriya]

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

[Mi2n]

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]

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 · Accepted solution]

@NehaVageriya is this resolved or need further help?

[Mi2n]

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

[parry2k]

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

Thanks,

P

[NehaVageriya]

Hi, Fiscal year starts at 1 July