cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Beginner_2020
Helper V
Helper V

Previous Fiscal Year automatically

Hi Power BI Community!
I have these two cards:

Beginner_2020_0-1623388988256.png

 

Fiscal Year Measure =  TOTALYTD([Rate (monthly)],Dates[Date],"9/30") 

My Fiscal Year starts at 01.10 till 30.09. Everything works right but now I also want to show the last Fiscal Year.

I use the advanced filter on date = Date is before 01.10.2020. But this will not work for next fiscal year.
The relativ date filter does only have year and calendar year but not Fiscal Year.

What can I do?

1 ACCEPTED SOLUTION
ERD
Solution Sage
Solution Sage

Hi @Beginner_2020 ,

You can try this

Prev FY =  
CALCULATE (
    [Rate (monthly)],
    SAMEPERIODLASTYEAR ( DATESYTD ( Dates[Date], "9/30" ) )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
ERD
Solution Sage
Solution Sage

Hi @Beginner_2020 ,

You can try this

Prev FY =  
CALCULATE (
    [Rate (monthly)],
    SAMEPERIODLASTYEAR ( DATESYTD ( Dates[Date], "9/30" ) )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

View solution in original post

BA_Pete
Super User II
Super User II

Hi @Beginner_2020 ,

 

You'll need to set up a calendar table that has fields for your fiscal year in it. Relate calendar[Date] to factTables[Date].

 

Your fiscal year field will look something like this when implemented in Power Query:

addFinYear = Table.AddColumn(addDateKey, "finYear", each Date.Year([date]+#duration(275,0,0,0))),

 

In this example, the '+#duration(275,0,0,0)' part is adding 275 days to the calendar row date as my fiscal year increases on 1st April of the calendar year. You can adjust the number of days here to something like 92 (maybe 93) to increase the fiscal year at 1st October on the calendar row.

 

Once you have that, you create a relative fiscal year field something like this:

addRelativeFY = Table.AddColumn(addFinYear, "relativeFY", each [finYear] - Date.Year(Date.Today+#duration(275,0,0,0))),

 

Again, you adjust the #duration section to the number of days between 1st October and 1st January.

You can then use this relative fiscal year field in filters and calculations, for example:

_salesLY = 
CALCULATE(
  SUM(yourTable[Sales]),
  calendar[relativeFY] = -1
)

 

Setting up a proper calendar table and relating it to your fact tables will also allow you to use time intelligence functions such as SAMEPERIODLASTYEAR if you just want the relative LY period, just make sure to use the calendar[Date] field in function arguments instead of factTables[Date].

 

Pete

Washivale
Resolver V
Resolver V

@Beginner_2020 : try with

Last FY : Calculate([Fiscal Year Measure],SAMEPERIODLASTYEAR(Dates[Date]))

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors