Solved: Re: FY running total for prior 3 fiscal years (tab...

Marcus2 · ‎02-01-2025

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

v-nuoc-msft

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.

View solution in original post

v-nuoc-msft · ‎02-03-2025

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.

Marcus2

Thanks @v-nuoc-msft, 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. 🙂

v-nuoc-msft

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.

Marcus2

Great thanks @v-nuoc-msft , 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?

v-nuoc-msft

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.

Marcus2

Thanks, slicer works with this DAX @v-nuoc-msft , Please see your random number screenshot below... the running total is not increasing. Thanks again.

v-nuoc-msft

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.

techies · ‎02-02-2025

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

)

Marcus2

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.

Marcus2 · ‎02-02-2025

@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

DataNinja777 · ‎02-01-2025

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,

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

Helpful resources

Join us at the Microsoft Fabric Community Conference

Join our Community Sticker Challenge 2025

Power BI Monthly Update - January 2025

Fabric Community Update - January 2025

New Offer! Become a Certified Fabric Data Engineer

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

Helpful resources

Join us at the Microsoft Fabric Community Conference

Join our Community Sticker Challenge 2025

Power BI Monthly Update - January 2025

Fabric Community Update - January 2025