Dynamic formula as calculated column to show current FY over time

[maneschr2022]

Hi,

I just wondering if anyone can help me with this one.

I have a Calendar table where I have used a calculated column to use it as a filter on visuals to display current calendar year. 

The DAX is:  datediff(CalendarTable[Date],TODAY(),YEAR)

This formula allows to show 0 when the calendar data refers to the current year, 1 for previous year, 2 for two years ago and so on

Now my question is, Is there an effective way to get the same result but for Financial Year? (FY as the period from 01/07 of a specific year to 30/06 of the next year)

So I need to find out a formula that will dynamically change every FY period to show 0 if the calendar table data relates to the current FY data, and same for previous FY: 1 at least.

Thanks for your help!

[maneschr2022 · Accepted solution]

Solved: Dynamically how to filter current fiscal year thro... - Microsoft Power BI Community

I found a good solution on this post.

[maneschr2022]

Hi @FreemanZ 

Thanks for your reply!

I did some testing, and at the moment for the formula that you suggested, shows value 1 from 01.07.2022 until 30.06.2023 and then changes to value 0 from 01.07.2023. Which is not ideal for me, I want to be able to use these values as filter in visuals .

For example on one visual displaying current FY data by using visual filter this column and using always the same value (I dont mind 0 or 1 as value)

But for example if I put myself in the scenario where the actual day is now Sept 2023 and the formula is going to show 0 instead of the value 1 for Current FY, I had to do a manual change on the filter of the visual which is not dynamic as I would like.
Hope you can help me out with this one!

Thanks!

[FreemanZ]

hi @maneschr2022 

try like:

Column =

VAR _year=YEAR(CalendarTable[date])

VAR _fyear =

IF(

    MONTH(CalendarTable[date])<=6,

    _year-1, _year

)

RETURN

YEAR(TODAY()) - _fyear