cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: fiscal year flag

@NehaVageriya is this resolved or need further help?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

7 REPLIES 7
Highlighted
Frequent Visitor

Re: fiscal year flag

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

Highlighted
Super User IV
Super User IV

Re: fiscal year flag

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

 

Thanks,

P






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: fiscal year flag

Hi, Fiscal year starts at 1 July

Highlighted
Resolver II
Resolver II

Re: fiscal year flag

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.

Highlighted
Frequent Visitor

Re: fiscal year flag

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 

Highlighted
Resolver II
Resolver II

Re: fiscal year flag

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

Highlighted
Super User IV
Super User IV

Re: fiscal year flag

@NehaVageriya is this resolved or need further help?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors