Dynamic Fiscal Year Calculation

brankocareer — Mon, 29 Jul 2024 21:57:47 GMT

Hi all,

I am trying to do a dynamic fiscal year calculation. On the top of report I added a Date slicer defind as "before", aiming to let user to choose one specific day. My goal is: I want to show the whole fiscal year's values. Our fiscal year starts from April 1st.
For example:
if choose Feb 20, 2023 on the slice, I want to see from April 1, 2022 to March 31,2023.

if choose June 20, 2023 on the slice, I want to see from April 1, 2023 to March 31, 2024.

The measeure I created is as following:

This_FY Bonus =

VAR _MaxSelectedDate = MAX('Date'[Date])

VAR _FiscalYear = IF(MONTH(_MaxSelectedDate) < 4, YEAR(_MaxSelectedDate) - 1, YEAR(_MaxSelectedDate))

RETURN

CALCULATE( SUM('OS + PNG'[BONUSES]),

FILTER( 'Date',
'Date'[Date] >= DATE(_FiscalYear, 4, 1) &&

'Date'[Date] <= DATE(_FiscalYear+1, 3,31)))

But I can not get the result I want. Really need your help.
Current result:

Result expecting, if choose Feb 16 2023, the March 2023 will be showen as well:

19 Feb 06	$3,619,537.67
19 Mar 06	$11,278,136.07
19 Mar 20	$5,860,419.85
19 Apr 03	$1,389,568.54
19 Apr 24	$4,976,952.53
19 May 08	$3,331,814.44
19 May 22	$3,417,977.28
19 Jun 05	$1,654,981.04
19 Jun 19	$2,704,352.06
19 Jul 03	$6,802,255.29
19 Jul 17	$6,816,753.38
19 Jul 31	$7,800,556.66
19 Aug 14	$1,797,151.88
19 Aug 28	$12,393,032.26
19 Sep 11	$1,906,123.33
19 Sep 25	$5,090,739.06
19 Oct 09	$4,074,968.36
19 Oct 23	$4,121,353.52
19 Nov 06	$5,449,207.69
19 Nov 20	$1,599,207.13
19 Dec 04	$4,052,434.05
19 Dec 18	$3,907,792.12
20 Jan 08	$9,441,104.33
20 Jan 22	$1,745,923.27
20 Feb 05	$5,128,284.33
20 Mar 04	$5,709,325.39
20 Mar 18	$965,342.13
20 Apr 01	$2,810,598.47
20 Apr 15	$0.00
20 Apr 29	$0.00
20 May 13	$0.00
20 May 27	$0.00
20 Jun 10	$0.00
20 Jun 24	$0.00
20 Jul 08	$0.00
20 Jul 22	$0.00
20 Aug 05	$0.00
20 Aug 19	$0.00
20 Sep 02	$0.00
20 Sep 16	$0.00
20 Sep 30	$0.00
20 Oct 14	$0.00
20 Oct 28	$0.00
20 Nov 18	$1,998,983.80
20 Dec 02	$1,616,843.92
16 Dec 20	$0.00
21 Jan 13	$4,492,359.84
21 Jan 27	$0.00
21 Feb 10	$4,548,727.57
21 Mar 10	$3,747,379.17
21 Mar 24	$1,469,906.30
21 Apr 07	$2,102,504.15
21 Apr 21	$5,767,018.88
21 May 05	$15,423,992.32
21 May 19	$4,252,177.72
21 Jun 02	$3,030,348.80
21 Jun 16	$2,489,445.68
21 Jul 07	$10,762,752.60
21 Jul 21	$991,745.18
21 Aug 04	$1,555,627.79
21 Aug 18	$3,908,501.28
21 Sep 01	$4,245,005.72
21 Sep 15	$4,003,440.00
21 Sep 29	$3,381,688.80
21 Oct 13	$4,438,652.61
21 Oct 27	$10,078,439.04
21 Nov 17	$8,276,626.74
21 Dec 01	$3,925,746.68
21 Dec 15	$5,302,071.11
22 Jan 12	$28,246,474.91
22 Jan 26	$9,584,851.22
22 Feb 09	$12,462,306.72
22 Mar 09	$9,670,219.44
22 Mar 23	$8,636,068.82
22 Apr 06	$16,350,587.57
22 Apr 20	$5,099,763.79
22 May 04	$9,341,941.33
22 May 18	$6,882,705.30
22 Jun 01	$13,641,269.76
22 Jun 15	$9,184,748.27
22 Jun 29	$9,613,568.42
22 Jul 13	$11,984,403.11
22 Jul 27	$17,453,721.52
22 Aug 10	$10,093,014.49
22 Aug 24	$6,862,732.36
22 Sep 07	$2,849,245.68
22 Sep 21	$15,789,531.66
22 Oct 05	$59,494,625.16
22 Oct 19	$12,053,467.17
22 Nov 02	$20,227,548.72
22 Nov 16	$11,678,592.04
22 Nov 30	$14,626,252.72
22 Dec 14	$11,917,736.32
23 Jan 11	$26,737,985.95
23 Jan 25	$10,515,006.14
23 Feb 08	$17,074,825.53
23 Mar 08	$22,044,513.70
23 Mar 22	$40,303,746.96
23 Apr 05	$25,668,243.85
23 Apr 19	$12,395,220.01
23 May 03	$3,574,554.66
23 May 17	$24,214,672.39
23 May 31	$11,023,703.84
23 Jun 14	$4,303,058.14
23 Jun 28	$7,989,877.44
23 Jul 12	7,958,388.48
23 Jul 26	9,359,469.14
23 Aug 09	74,230,640.89
23 Aug 23	10,730,182.02
23 Sep 06	6,949,148.62
23 Sep 20	3,506,014.89
23 Oct 04	5,868,948.36
23 Oct 18	5,567,986.30
23 Nov 01	9,079,104.45
23 Nov 15	9,319,191.34
23 Nov 29	7,651,828.76
23 Dec 13	14,052,009.68
24 Jan 10	19,754,328.61
24 Jan 24	10,528,337.85
24 Feb 07	21,582,909.32
24 Mar 06	12,226,226.63
24 Mar 20	58,716,497.92
24 Apr 03	23,040,685.34
24 Apr 17	13,531,800.86
24 May 01	14,780,998.79
24 May 15	13,194,504.00
24 May 29	7,600,276.05

Re: Dynamic Fiscal Year Calculation

Anonymous — Tue, 30 Jul 2024 02:45:43 GMT

Hi brankocareer

After my test, I think your formula is correct, but you need to add a table as a slicer. The following is my test process.

1. Create a calculated table as the slicer

no relationship between two tables

2. Slightly modify your measure

This_FY Bonus = VAR _MaxSelectedDate = MAX('Slicer'[Date]) VAR _FiscalYear = IF(MONTH(_MaxSelectedDate) < 4, YEAR(_MaxSelectedDate) - 1, YEAR(_MaxSelectedDate)) RETURN CALCULATE( SUM('Table'[Value]), FILTER( 'Table', 'Table'[Date] >= DATE(_FiscalYear, 4, 1) && 'Table'[Date] <= DATE(_FiscalYear+1, 3, 31)) )

Output:

Best Regards,
Yulia Xu

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

Re: Dynamic Fiscal Year Calculation

brankocareer — Tue, 30 Jul 2024 15:37:54 GMT

Anonymous

Thank you for your reply! Your method works on most dates, but when you choose any day in April, it shows only April and May. Please help!

Re: Dynamic Fiscal Year Calculation

Anonymous — Thu, 01 Aug 2024 07:31:04 GMT

Hi brankocareer

After my test, this is because the sample data you provided only has data up to May of 24. I added data from later dates to the data, and the results are as follows:

Output:

In addition, I found that the newly created slicer table needs to be modified to a continuous timetable.

Slicer = CALENDAR(DATE(2019, 1, 1), TODAY())

Best Regards,
Yulia Xu

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

topic Re: Dynamic Fiscal Year Calculation in DAX Commands and Tips

Dynamic Fiscal Year Calculation

Re: Dynamic Fiscal Year Calculation

Re: Dynamic Fiscal Year Calculation

Re: Dynamic Fiscal Year Calculation