topic Dynamic running totals based on Financial year filter selection in Report Server

Dynamic running totals based on Financial year filter selection

visubabu24 — Thu, 26 Oct 2023 04:23:41 GMT

Hi All,
I am working on a requirement where if a financial year is selected the Values less than equal to selected FY should sum up for all distinct categories .

For Example :

RowNo	Category	Date	Financial Year	Amount
1	Furnitures	10/10/2023	2023-24	500
2	Decors	10/09/2022	2022-23	400
3	Furnitures	10/05/2021	2021-22	300
4	Paints	12/06/2020	2020-21	200
5	Decors	10/05/2020	2020-21	100

If FY 2023-24 is selected in the filter the categories with latest dates should get aggregated
in this case row no 1,2,4 should be aggregated and if FY 2022-23 is selected row no 2,3,4 should be aggregated .
Though I am using SQL source I am restricted to use parameters and stored procs.
I am Kind of run out of ideas . Your help is appreciated
Thanks in Advance

Re: Dynamic running totals based on Financial year filter selection

lbendlin — Mon, 06 Nov 2023 00:58:49 GMT

Do you mean running totals or simple sums? What would the expected outcome look like for your two scenarios?

Re: Dynamic running totals based on Financial year filter selection

visubabu24 — Mon, 06 Nov 2023 01:29:21 GMT

HI lbendlin , it's only the running total . If FY 2023-24 is selected in filter I should get sum of row nos. 1,2 and 4 so the total is 1100.
If the FY 2022-23 is selected in the filter row nos 2,3,4 needs to sum up hence the total is 900

Re: Dynamic running totals based on Financial year filter selection

lbendlin — Mon, 06 Nov 2023 01:33:39 GMT

You get that for free in the UI, no code required. Give it a try.

Re: Dynamic running totals based on Financial year filter selection

visubabu24 — Mon, 06 Nov 2023 01:33:59 GMT

Here the blocker for me is in running total we should consider only the category of latest FY.

for example If I am selecting 2023-24, Furniture category is already there in 2023-24 hence our calculation should ignore 2021-22, decors category already available in 2021_2022 hence calculation should ignore decors in fy 2021-22

Re: Dynamic running totals based on Financial year filter selection

visubabu24 — Mon, 06 Nov 2023 01:43:59 GMT

I tried DAX and I am able to get the running total for all FYs but I am unable to filter out the condition which I have mentioned.

Re: Dynamic running totals based on Financial year filter selection

lbendlin — Mon, 06 Nov 2023 01:53:14 GMT

OK, now I got it

see attached.

Re: Dynamic running totals based on Financial year filter selection

visubabu24 — Mon, 06 Nov 2023 02:02:01 GMT

Thank You So much Ibendlin .