How to cycle FY running total (Oct-Sept) with end date selected

[Marcus2]

Hi, my current DAX doesn't cycle Oct-Sept.  How can I update this DAX to get my running total to cycle Oct-Sept and keep my single selection slicer end date... and keep last 3 years of data from slicer selection.  

My running total result: 

Running Total =

var _NumOfMonths = -36

var _ReferenceDate = Max('DateTable'[Date])

var _ReferencePrevDate = Max ('DateTable(2)'[Date])

var _PreviousDates =

FILTER(

DatesInPeriod('DateTable(2)'[Date],

_ReferenceDate,

_NumOfMonths,

MONTH),

'DateTable(2)'[Date] <= _ReferencePrevDate

)

var _Result =

 if( _ReferencePrevDate <= _ReferenceDate,

Calculate(

    SUMX(ExcelSheet,ExcelSheet[Value]),

    REMOVEFILTERS('DateTable'),

_PreviousDates,

USERELATIONSHIP('DateTable'[Date], 'DateTable(2)'[Date])

)

 )

Return

_Result

Desired results:

Date	FY	FY Running Total
10/31/2022	2023	21
11/30/2022	2023	110
12/31/2022	2023	197
1/31/2023	2023	263
2/28/2023	2023	329
3/31/2023	2023	422
4/30/2023	2023	479
5/31/2023	2023	529
6/30/2023	2023	590
7/31/2023	2023	617
8/31/2023	2023	709
9/30/2023	2023	768
10/31/2023	2024	54
11/30/2023	2024	108
12/31/2023	2024	125
1/31/2024	2024	150
2/29/2024	2024	184
3/31/2024	2024	195
4/30/2024	2024	250
5/31/2024	2024	327
6/30/2024	2024	395
7/31/2024	2024	457
8/31/2024	2024	515
9/30/2024	2024	580
10/31/2024	2025	13
11/30/2024	2025	65
12/31/2024	2025	143
1/31/2025	2025	183
2/28/2025	2025	271
3/31/2025	2025	297
4/30/2025	2025
5/31/2025	2025
6/30/2025	2025
7/31/2025	2025
8/31/2025	2025
9/30/2025	2025

[Marcus2 · Accepted solution]

Thanks, I found what I needed... took me a while.  I'm new to DAX

[lbendlin]

You already have a Calendar table. It probably has columns for Fiscal Year etc.  Use these as filters for your running totals.

[Marcus2]

Thanks @lbendlin, have an example?  🙂

[lbendlin]

That's not how this works. Other way round.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...