Forum Discussion
Dynamic Fiscal Year Calculation
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.
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.
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.