Hi, I have a dataset with "Financial year", Month and Quarter filters and the spend amount for each month as another field.
I want to show the total spend for the last 2 full financial years (12 months of data). First I am trying to get the last 2 full fin years.
I am trying these expressions to calculate first and second financial year and storing the values in a variable.
The problem is when I select a month in the filter, the resulting financial year changes because I only have last 36 calendar months of data. So, if financial year is starting in Jan, my app doesn't have data for Jan 2019 and Feb 2019. It shows 2021 and 2020 as last 2 full fin years alright when I don't have anything selected in Month filter. As soon as I select Month as Jan, it starts showing 2021 and 2022 as last 2 fin years.
=FirstSortedValue( {<[Financial year]=, Month=, Quarter=>} DISTINCT TOTAL [Financial year], -aggr( {<[Financial year]=, Month=, Quarter=>} COUNT(DISTINCT Month)+[Financial year], [Financial year]) ,1 )
=FirstSortedValue( {<[Financial year]=, Month=, Quarter=>} DISTINCT TOTAL [Financial year], -aggr( {<[Financial year]=, Month=, Quarter=>} COUNT(DISTINCT Month)+[Financial year], [Financial year]) ,2)
