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.
Solved! Go to Solution.
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.
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
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.