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

Dynamic Fiscal Year Calculation

brankocareer — Thu, 18 Jul 2024 15:12:34 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. The measeure 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]),

'Date'[FiscalYear] = FiscalYear )

If I choose a date, for example April 24, 2024, I assume get only summary of [BONUSES] in 2024, but all years are listed. I am wondering how to revise the DAX, to make the "Year" solid?

I am expecting like this:

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 — Fri, 19 Jul 2024 02:12:04 GMT

Hi, brankocareer

You can try the following methods. Create a date table.

Measure = CALCULATE(SUM('Table'[Bonus]),FILTER(ALL('Table'),[Date]<=MAX('Date'[Date])),FILTER(ALL('Date'),[Fiscal Year]=YEAR(MAX('Date'[Date]))))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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 — Fri, 19 Jul 2024 20:09:24 GMT

Hi Charlotte,

I don't get you, could you share your pbix file?

Re: Dynamic Fiscal Year Calculation

brankocareer — Fri, 19 Jul 2024 20:35:21 GMT

Hi Charlotte,

Your method works at year level. If you add month, it won't work.

Re: Dynamic Fiscal Year Calculation

Anonymous — Tue, 23 Jul 2024 06:02:18 GMT

Hi, brankocareer

You can try the following methods.

Please see the attachment.

Best Regards,

Community Support Team _Charlotte

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 — Wed, 24 Jul 2024 15:04:13 GMT

Hi Anonymous ,

Thank you for your reply! Your method works at year level. However, if you add month, it won't work. How to address this issue?

Thank you!

Branko

Re: Dynamic Fiscal Year Calculation

brankocareer — Wed, 24 Jul 2024 15:44:48 GMT

Hi Anonymous

I really need your help, I still can not get what I want, even I revised my measure. I already add a [Fiscal Year ] column in 'Date' table. What I need is only one year (2022 in this case) and months show up, other than all years.

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(

ALL('Date'),

'Date'[FiscalYear] = _FiscalYear &&

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

'Date'[Date] <= _MaxSelectedDate

)

Could you please help me out?

Thank you!

Branko

Re: Dynamic Fiscal Year Calculation

Anonymous — Thu, 25 Jul 2024 02:35:31 GMT

Hi, brankocareer

You can try the following methods.

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'[Fiscal Year] = _FiscalYear && 'Date'[Date] >= DATE(_FiscalYear, 4, 1) && 'Date'[Date] <= _MaxSelectedDate))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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 — Thu, 25 Jul 2024 17:17:07 GMT

Hi Anonymous ,

Let me clearify my goal: I want to show the whole fiscal year's values. Our fiscal year starts from April 1st.
For example:
if choose March 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.

Current my measure is :

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'[FiscalYear] = _FiscalYear &&

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

'Date'[Date] <= _MaxSelectedDate

)

)
It still doesn't work. Did I do anything wrong?
Thank you for your time and support!

Branko

Re: Dynamic Fiscal Year Calculation

brankocareer — Thu, 25 Jul 2024 17:18:46 GMT

Sorry, I pasted the wrong measure, current my measure is :

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)))

Re: Dynamic Fiscal Year Calculation

Anonymous — Thu, 01 Aug 2024 07:44:56 GMT

Hi, brankocareer

You can try the following methods.

Measure = CALCULATE(SUM('OS + PNG'[BONUSES]),ALLEXCEPT('Date','Date'[Fiscal Year]))

Measure 2 = Var _FY=CALCULATE(MAX('Date'[Fiscal Year]),FILTER(ALL('Date'),[Date]=MAX('Date'[Date]))) Return CALCULATE([Measure],FILTER(ALL('Date'),[Fiscal Year]=_FY))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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