Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NehaVageriya
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

@NehaVageriya is this resolved or need further help?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
NehaVageriya
Frequent Visitor

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

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 

@NehaVageriya is this resolved or need further help?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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

 

Thanks,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Hi, Fiscal year starts at 1 July

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.