Rolling Average DAX with Timeframe Based on Selected FY / Month

[tlo11]

I have come across one DAX question that I just cannot solve myself with Google and/or my existing references:

I want to calculate fluctuation rate which is calculated by dividing all leavers by the average FTE present during a set time period. This set period is supposed to be a financial year (starting in July, ending in June) which deviates from a traditional CY.

So for my use case, the user selects a month in a FY (lets say June in FY23, which is the last month of the FY) and is supposed to see the average fluctuation rate for the FY23. If the user selects an earlier month in the same FY (i.e. March in FY23), the fluctuation rate should be calculated as an average from start of FY23 till that month, i.e. March FY23.

How would you realize this? In addition, I want to have a measure that will also show the value from previous FY but at the same month, i.e. if March FY23 is selected, the additional measure should show the average fluctuation rate from July FY22 to March FY22.

[tlo11]

Maybe its me but I dont get it to work- it always shows me the exact value of the rate in the previous year but not the average of the previous 12 months 😕

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Read about the new Windowing functions. Introducing window functions in DAX - SQLBI