Running Total (prior 3 fiscal years)

[Marcus2]

Hi,  how can I get 3 prior years from the FY selected.  My goal is to select a single year (2024 in this example) and view data three years prior (with final aggregation month selected in a second slicer).   Is there a DAX for this?

FY	Month	Value
2022	Oct	5
2022	Nov	5
2022	Dec	5
2022	Jan	5
2022	Feb	5
2022	Mar	5
2022	Apr	5
2022	May	5
2022	Jun	5
2022	Jul	5
2022	Aug	5
2022	Sep	5
2023	Oct	5
2023	Nov	5
2023	Dec	5
2023	Jan	5
2023	Feb	5
2023	Mar	5
2023	Apr	5
2023	May	5
2023	Jun	5
2023	Jul	5
2023	Aug	5
2023	Sep	5
2024	Oct	5
2024	Nov	5
2024	Dec	5
2024	Jan	5
2024	Feb
2024	Mar
2024	Apr
2024	May
2024	Jun
2024	Jul
2024	Aug
2024	Sep

Desired table result with FY2024 and Nov slicer selections:

FY	Month	FYTD
2022	Oct	5
2022	Nov	10
2022	Dec	15
2022	Jan	20
2022	Feb	25
2022	Mar	30
2022	Apr	35
2022	May	40
2022	Jun	45
2022	Jul	50
2022	Aug	55
2022	Sep	60
2023	Oct	5
2023	Nov	10
2023	Dec	15
2023	Jan	20
2023	Feb	25
2023	Mar	30
2023	Apr	35
2023	May	40
2023	Jun	45
2023	Jul	50
2023	Aug	55
2023	Sep	60
2024	Oct	5
2024	Nov	10
2024	Dec
2024	Jan
2024	Feb
2024	Mar
2024	Apr
2024	May
2024	Jun
2024	Jul
2024	Aug
2024	Sep

[Poojara_D12]

Hi @Marcus2 

I think you need to:

1. Capture the selected FY from a slicer.
2. Determine the three prior years dynamically.
3. Filter the data to include only the selected month up to the chosen month in all relevant years.

Please use this dax;

Filtered Value =
VAR SelectedFY = SELECTEDVALUE('FY_Table'[FY]) -- Captures the selected fiscal year
VAR SelectedMonth = SELECTEDVALUE('FY_Table'[Month]) -- Captures the selected month
VAR StartFY = SelectedFY - 3  -- Compute three years prior

RETURN
CALCULATE(
    SUM('FY_Table'[Value]), -- Aggregating the values
    'FY_Table'[FY] >= StartFY && 'FY_Table'[FY] < SelectedFY, -- Includes three prior years
    'FY_Table'[Month] <= SelectedMonth -- Filters months up to the selected one
)

• If you want to display this filtered data in a table visual, create a calculated column instead of a measure.
• If the fiscal year starts in a month other than January (e.g., July), adjust the FY logic accordingly.

[sanalytics · Accepted solution]

@Marcus2 
Dont understand the  your exact output what you are looking for. 
I have assumed 3 scenario output. Below are the details

1) Lets you are looking for total aggegate value for 3 year's prior and same month.. For example, if you select 2025 in year slicer and sep in Month slicer. You want to sum up prior 3 years (2022,2023,2024) sep's value then below code will work

Total Aggregate Value = 
VAR _Selection = MAX( Data[FY] )
VAR _1stYr = _Selection - 1
VAR _2ndYr = _Selection - 2
VAR _3rdYr = _Selection - 3
VAR _tbl = 
UNION(
ROW("@yr",_1stYr),
ROW( "@yr",_2ndYr),
ROW("@yr",_3rdYr)
 )
VAR _vtable = 
TREATAS(
    _tbl,Data[FY]
)
VAR _Result = 
CALCULATE(
    SUM(Data[Value] ),
    _vtable
)
RETURN
_Result

2) If you want to show,, total value from 2022 sep to 2024 sep. The below is code

Full Data Value = 
SUMX(
VALUES( Data[Index] ),
VAR _endIndex = MAX( Data[Index] ) - 12
VAR _StarIndex = MAX( Data[Index] ) - 36
VAR _tbl = 
FILTER(
ALL( Data ),
Data[Index] <= _endIndex &&
Data[Index] >= _StarIndex
)
VAR _check = 
COUNTROWS( _tbl )
VAR _Result = 
SUMX(
    _tbl,[Value]
)

RETURN
_Result
)

3) if you want to only show only 3 years (2022 sep,2023 sep,2024 sep) data in a table visual..then check the below pbix file for steps. Page name -> Showdata.

Below screenshot

Attached pbis file for your reference.

Hope it helps

Regards

sanalytics

[bhanu_gautam]

@Marcus2 , First create a measure to capture selected month and year

SelectedFY =
SELECTEDVALUE('Table'[FY])

SelectedMonth =
SELECTEDVALUE('Table'[Month])

Now create a measure to calculate on the basis of this

Sum3YearsPrior =
VAR CurrentFY = [SelectedFY]
VAR CurrentMonth = [SelectedMonth]
VAR PriorFY1 = CurrentFY - 1
VAR PriorFY2 = CurrentFY - 2
VAR PriorFY3 = CurrentFY - 3
VAR EndDate = DATE(CurrentFY, MONTH(DATEVALUE("1 " & CurrentMonth & " " & CurrentFY)), DAY(DATEVALUE("1 " & CurrentMonth & " " & CurrentFY)))
VAR StartDate = DATE(PriorFY3, MONTH(DATEVALUE("1 " & CurrentMonth & " " & PriorFY3)), DAY(DATEVALUE("1 " & CurrentMonth & " " & PriorFY3)))
RETURN
CALCULATE(
SUM('Table'[Value]),
DATESBETWEEN('Table'[Date], StartDate, EndDate)
)