Forum Discussion
Dynamic Fiscal Year Calculation
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:
'Date'[Date] >= DATE(_FiscalYear, 4, 1) &&
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 |
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!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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.