One thing that can seem like a limitation of Power BI is its calendar date table – often, you’ll need to calculate results across a custom financial year, but only the standard date table is available.
You may initially think that you need a new date table, but you don’t. You can use DAX in either measures or calculated columns to get your calculation to work around financial years.
This video showcases how you can calculate a cumulative total that works across your financial year. In this case we are going to work with the powerful time intelligence functions set – in particular, DATESYTD. By using this inside the CALCULATE statement we can almost effortlessly move from a calendar year calc to a financial year one.
So it’s really not that difficult in this case. You just need to understand what CALCULATE does and then how to apply time intelligence functions.
Check out this video to learn how to apply a custom financial year to your own models.
You may also want to explore how you can make sure that your months and quarters are all aligned with your financial years. This is done via calculated columns and will be something I’ll dive into in another post.