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
Hell-1931
Helper II
Helper II

How to set Fiscal Year value (Fiscal Year to Date) in Power BI Desktop

Hi, 

I am new in Power BI (sorry for this type of questions)

 

I have [Date] table and a Report.

In that Report I created a Slicer, where I can select a date according to which the data on my report will be shown.

 

My Fiscal Year dates - 07/01/2019 - 06/30/2020 = FY2020

My goal is to set a default Fiscal Year to Date in my Slicer, so that they would automatically change when the date will become after 6/30/2020, for example - change to FY2021, etc

 

How do I do that?

 

My [Date] table has [YearMonth] field, I am using for my slicer:

Capture.JPG

 

 

1 ACCEPTED SOLUTION
shaunwilks
Helper V
Helper V

 

If I understand it correctly you need to add a Column to you Date table and Label it "Fiscal Year".

 

That column could be something like..

Year = If('Date Calendar'[Cal Month Nbr]<=6,'Date Calendar'[Cal Year],'Date Calendar'[Cal Year]+1)

 
That way in your slicers you can display the Fiscal year for selection.
 
If you are looking at YTD measures to calculate totals for the Fiscal year look at TOTALYTD and SAMEPERIODLASTYEAR functions. the TOTALYTD has a parameter where you specify the "year end day and month".
So its does it calcs usiong the current date and time to show values between todays date, dating back to the year end date and month you specified. 

View solution in original post

5 REPLIES 5
shaunwilks
Helper V
Helper V

 

If I understand it correctly you need to add a Column to you Date table and Label it "Fiscal Year".

 

That column could be something like..

Year = If('Date Calendar'[Cal Month Nbr]<=6,'Date Calendar'[Cal Year],'Date Calendar'[Cal Year]+1)

 
That way in your slicers you can display the Fiscal year for selection.
 
If you are looking at YTD measures to calculate totals for the Fiscal year look at TOTALYTD and SAMEPERIODLASTYEAR functions. the TOTALYTD has a parameter where you specify the "year end day and month".
So its does it calcs usiong the current date and time to show values between todays date, dating back to the year end date and month you specified. 

Thank you, the 1st suggested "Fiscal Year" calculation worked for all the previous years!

 

But now - for the Current Fiscal Year only -  I need the end of current Fiscal Year to be - not until 6/30, but until the end of the Previous month -

So, if today is 8/21/2019, I need my end of FY2020 to be 7/31/2019.

Current FY = FY-to-date (til the end of the prev month)

 

Are there possibilities to do this?

 


@shaunwilks wrote:

 

If I understand it correctly you need to add a Column to you Date table and Label it "Fiscal Year".

 

That column could be something like..

Year = If('Date Calendar'[Cal Month Nbr]<=6,'Date Calendar'[Cal Year],'Date Calendar'[Cal Year]+1)

 
That way in your slicers you can display the Fiscal year for selection.
 
If you are looking at YTD measures to calculate totals for the Fiscal year look at TOTALYTD and SAMEPERIODLASTYEAR functions. the TOTALYTD has a parameter where you specify the "year end day and month".
So its does it calcs usiong the current date and time to show values between todays date, dating back to the year end date and month you specified. 

 

I'll try to clarify -

Now, I updated and have 2 slicers in my Report - "Fiscal Year" and "Year-Month":

Capture1.JPG

All the Fiscal Years work good - 07/01 - 06/30 (thank you for your sugestions), except of the very Current Fiscal Year.

My goal - when I'll select current Fiscal Year = 2020, I want to see the Report's Data - for Year-Month = 2019-07 only - until 07/31/2019, while I see them currently until 06/2020

 

So, for my current FY only - I need to modify my slicer(s) so that I could only see the report's data

- 07/01/2019 - 07/31/2019 (which is the end of the previous month)

 

If today would've been 09/21, I need to see my data for FY 2020 as 07/01/2019 - 08/31/2019 , etc...

 

 

 

There are many many ways to kill a fly in this space.

 

Id probably recommend setting a flag in the date calendar using a measure or calculated column similar to my last comment.  But instead of using the Date calendar date use the TODAY() function.

That gives you the month end date for last month,

 

You need only then have anotehr calculated column that checked if the Date in the date calender was > than the Last Month End Date and if se set the field to a N, else Y.  Then drag this new field to the Visuals Filter and say you need on those records with a Y value. 

Not sure I understand that requirement.

It would suggest that the Year end is fluid and just the end of the previous month.

 

If you want to add a formula to the date table, that is the last day of the previous month then it would be something like...

 

Previous Month End Date = Date(Year('Date Calendar'[Date]),MONTH('Date Calendar'[Date]),1)-1

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.