topic Re: FY running total for prior 3 fiscal years (table visual) in DAX Commands and Tips

FY running total for prior 3 fiscal years (table visual)

Marcus2 — Sat, 01 Feb 2025 15:43:57 GMT

Hi, how can I get 3 prior years from the FY and month selected. I've created a calendar table and used DAX, but current DAX doesn't stop aggregating at my selected end month (I also have to multiselect the years... goal is to select final year).

Here's my initial data:

Date	FY	Month	Value
10/31/2021	2022	Oct	5
11/30/2021	2022	Nov	5
12/31/2021	2022	Dec	5
1/31/2022	2022	Jan	5
2/28/2022	2022	Feb	5
3/31/2022	2022	Mar	5
4/30/2022	2022	Apr	5
5/31/2022	2022	May	5
6/30/2022	2022	Jun	5
7/31/2022	2022	Jul	5
8/31/2022	2022	Aug	5
9/30/2022	2022	Sep	5
10/31/2022	2023	Oct	5
11/30/2022	2023	Nov	5
12/31/2022	2023	Dec	5
1/31/2023	2023	Jan	5
2/28/2023	2023	Feb	5
3/31/2023	2023	Mar	5
4/30/2023	2023	Apr	5
5/31/2023	2023	May	5
6/30/2023	2023	Jun	5
7/31/2023	2023	Jul	5
8/31/2023	2023	Aug	5
9/30/2023	2023	Sep	5
10/31/2023	2024	Oct	5
11/30/2023	2024	Nov	5
12/31/2023	2024	Dec	5
1/31/2024	2024	Jan	5
2/29/2024	2024	Feb
3/31/2024	2024	Mar
4/30/2024	2024	Apr
5/31/2024	2024	May
6/30/2024	2024	Jun
7/31/2024	2024	Jul
8/31/2024	2024	Aug
9/30/2024	2024	Sep

Desired result with FY2024 and Nov slicer selections:

Date	FY	Month	FYTD
10/31/2021	2022	Oct	5
11/30/2021	2022	Nov	10
12/31/2021	2022	Dec	15
1/31/2022	2022	Jan	20
2/28/2022	2022	Feb	25
3/31/2022	2022	Mar	30
4/30/2022	2022	Apr	35
5/31/2022	2022	May	40
6/30/2022	2022	Jun	45
7/31/2022	2022	Jul	50
8/31/2022	2022	Aug	55
9/30/2022	2022	Sep	60
10/31/2022	2023	Oct	5
11/30/2022	2023	Nov	10
12/31/2022	2023	Dec	15
1/31/2023	2023	Jan	20
2/28/2023	2023	Feb	25
3/31/2023	2023	Mar	30
4/30/2023	2023	Apr	35
5/31/2023	2023	May	40
6/30/2023	2023	Jun	45
7/31/2023	2023	Jul	50
8/31/2023	2023	Aug	55
9/30/2023	2023	Sep	60
10/31/2023	2024	Oct	5
11/30/2023	2024	Nov	10
12/31/2023	2024	Dec
1/31/2024	2024	Jan
2/29/2024	2024	Feb
3/31/2024	2024	Mar
4/30/2024	2024	Apr
5/31/2024	2024	May
6/30/2024	2024	Jun
7/31/2024	2024	Jul
8/31/2024	2024	Aug
9/30/2024	2024	Sep

Re: FY running total for prior 3 fiscal years (table visual)

DataNinja777 — Sun, 02 Feb 2025 06:34:29 GMT

Hi Marcus2 ,

The most straightforward and standard way to calculate the Fiscal Year-To-Date (FYTD) running total for the prior three years while ensuring the accumulation stops at the selected month is by using TOTALYTD. This function automatically resets the total at the beginning of each fiscal year, making it the best choice for this scenario.

FYTD Running Total = VAR SelectedFY = SELECTEDVALUE( 'Calendar'[FY] ) VAR SelectedMonth = SELECTEDVALUE( 'Calendar'[Month] ) RETURN CALCULATE( TOTALYTD( SUM( 'Table'[Value] ), 'Calendar'[Date], "9/30" -- Adjust this if your fiscal year ends in a different month ), 'Calendar'[FY] >= SelectedFY - 3 && 'Calendar'[FY] <= SelectedFY, 'Calendar'[Month] <= SelectedMonth )

This formula ensures that the FYTD calculation correctly accumulates within each fiscal year while stopping at the selected month. The "9/30" parameter specifies that the fiscal year ends in September, so update it if your fiscal year follows a different cycle. This is the most standard approach and should work efficiently with your Power BI model.

Best regards,

Re: FY running total for prior 3 fiscal years (table visual)

Marcus2 — Sun, 02 Feb 2025 16:03:10 GMT

DataNinja777 thanks, I'm still getting the same results as before. Could it be my coloumn data types? With your DAX provided I get the following:

(FY2024 and Dec) slicer selections: (just one year)

FY	Month	FYTD
2024	Dec	15

Just FY2024 slicer selection: (just one year; it repeats the last month of available FY data)

FY	Month	FYTD
2024	Oct	5
2024	Nov	10
2024	Dec	15
2024	Jan	20
2024	Feb	20
2024	Mar	20
2024	Apr	20
2024	May	20
2024	Jun	20
2024	Jul	20
2024	Aug	20
2024	Sep	20

Current coloumn data types:

Calendar [Date] = Date/time

Calendar [FY] = Whole number

Calendar [FYMonthNumber] = Whole number

Calendar [MonthName] = Text

Calendar [MonthNumber] = Whole number

DataTable [Date] = Date/time

DataTable [FY] = Whole number

DataTable [FYMonthNumber] = Whole number

DataTable [MonthName] = Text

DataTable [MonthNumber] = Whole number

Re: FY running total for prior 3 fiscal years (table visual)

techies — Sun, 02 Feb 2025 17:00:26 GMT

please check this

YTD_Running_Total =

VAR SelectedFY = SELECTEDVALUE('DATE'[Year])

VAR SelectedMonth = SELECTEDVALUE('DATE'[Month])

VAR MaxDate =

MAXX(

FILTER(

ALL('DATE'),

'DATE'[Year] = SelectedFY &&

'DATE'[Month] = SelectedMonth

'DATE'[Date]

)

VAR StartDate = DATE(YEAR(MaxDate) - 2, 1, 1)

RETURN

CALCULATE(

SUMX(

FILTER(

ALL('DATE'),

'DATE'[Date] >= StartDate && 'DATE'[Date] <= MaxDate

value

)

Re: FY running total for prior 3 fiscal years (table visual)

Anonymous — Mon, 03 Feb 2025 09:30:51 GMT

Hi Marcus2

Thank you very much DataNinja777 or your prompt reply.

For your question, here is the method I provided:

Create a calendar table.

Calendar = ADDCOLUMNS ( CALENDAR (MIN('Table'[Date]), MAX('Table'[Date])), "FY", YEAR([Date]) + IF(MONTH([Date]) > 9, 1, 0), "Month", MONTH([Date]) )

Create a measure.

FYTD Value = VAR SelectedFY = SELECTEDVALUE('Calendar'[FY]) VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month]) VAR PriorYearsFYTD = CALCULATE ( SUM('Table'[Value]), FILTER( 'Calendar', 'Calendar'[FY] IN {SelectedFY - 1, SelectedFY - 2, SelectedFY - 3} ) ) RETURN SUMX( FILTER( ALL('Table'), 'Table'[FY] = MAX('Table'[FY]) && 'Table'[Date] <= MAX('Table'[Date]) ), PriorYearsFYTD )

Here is the result.

Regards,

Nono Chen

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

Re: FY running total for prior 3 fiscal years (table visual)

Marcus2 — Tue, 04 Feb 2025 20:09:05 GMT

Hi, I'm trying your DAX but I think something is missing in front of "value" near the last line. I'm getting a syntax error message.

Re: FY running total for prior 3 fiscal years (table visual)

Marcus2 — Wed, 05 Feb 2025 15:20:59 GMT

Thanks Anonymous, do you have a pbix file compatible with PowerBI Version 2.116? I tried a work around opening your file in PowerBI Service but slicers wouldn't control dates. 🙂

Re: FY running total for prior 3 fiscal years (table visual)

Anonymous — Thu, 06 Feb 2025 02:59:34 GMT

Hi Marcus2

I modified the code, you can try it again:

Calendar = ADDCOLUMNS ( SELECTCOLUMNS('Table', "Date", 'Table'[Date]), "FY", YEAR([Date]) + IF(MONTH([Date]) > 9, 1, 0), "Month", MONTH([Date]) )

FYTD Value = VAR SelectedFY = SELECTEDVALUE('Calendar'[FY]) VAR SelectedDate = SELECTEDVALUE('Calendar'[Date]) VAR PriorYearsFYTD = CALCULATE ( SUM('Table'[Value]), FILTER( 'Table', YEAR('Table'[Date]) IN {SelectedFY - 1, SelectedFY - 2, SelectedFY - 3} && 'Table'[Date] <= SelectedDate ) ) RETURN SUMX( FILTER( ALL('Table'), 'Table'[FY] = MAX('Table'[FY]) && 'Table'[Date] <= MAX('Table'[Date]) ), PriorYearsFYTD )

Regards,

Nono Chen

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

Re: FY running total for prior 3 fiscal years (table visual)

Marcus2 — Thu, 06 Feb 2025 16:29:30 GMT

Great thanks Anonymous , are you able to see slicer actions between months 9 and 1? Seems to function great at 10, 11, and 12. Also, I noticed if I replace 5s with random numbers for my source data... the FYTD running total doesn't increase perfectly. Are you able to swap the 5s with radom numbers?

Re: FY running total for prior 3 fiscal years (table visual)

Anonymous — Fri, 07 Feb 2025 01:51:20 GMT

Hi Marcus2

Try this:

FYTD Value = VAR SelectedFY = SELECTEDVALUE('Calendar'[FY]) VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month]) VAR PriorYearsFYTD = CALCULATE ( SUM('Table'[Value]), FILTER( 'Table', YEAR('Table'[Date]) IN {SelectedFY - 1, SelectedFY - 2, SelectedFY - 3} && 'Table'[Date] <= DATE(SelectedFY - 1, SelectedMonth, 31) ) ) RETURN SUMX( FILTER( ALL('Table'), 'Table'[FY] = MAX('Table'[FY]) && 'Table'[Date] <= MAX('Table'[Date]) ), PriorYearsFYTD )

If you need to replace "5" with a random number, you can select your data table in the Power Query Editor. Add a custom column and use the following formula to generate a random number:

= if [Value] = 5 then Number.RoundDown(Number.RandomBetween(1, 100)) else [Value]

And make sure the data type is a numeric type.

You can get:

Regards,

Nono Chen

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

Re: FY running total for prior 3 fiscal years (table visual)

Marcus2 — Sun, 09 Feb 2025 21:06:33 GMT

Thanks, slicer works with this DAX Anonymous , Please see your random number screenshot below... the running total is not increasing. Thanks again.

Re: FY running total for prior 3 fiscal years (table visual)

Anonymous — Mon, 10 Feb 2025 08:35:34 GMT

Hi Marcus2

Try this:

FYTD Value = VAR SelectedFY = SELECTEDVALUE('Calendar'[FY]) VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month]) VAR SelectedDate = DATE(SelectedFY - 1, SelectedMonth, 31) VAR PriorYearsFYTD = CALCULATE ( SUM('Table'[Custom]), FILTER( ALL('Table'), 'Table'[FY] = MAX('Table'[FY]) && 'Table'[Date] <= MAX('Table'[Date]) && YEAR('Table'[Date]) IN {SelectedFY - 1, SelectedFY - 2, SelectedFY - 3} && 'Table'[Date] <= SelectedDate ) ) RETURN IF( SELECTEDVALUE('Table'[Date]) <= SelectedDate, PriorYearsFYTD, BLANK() )

Here is the result.

Regards,

Nono Chen

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