Dynamic Fiscal Year Calculation

[brankocareer]

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

[brankocareer]

Hi Charlotte,

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

[v-zhangtin-msft]

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.

[v-zhangtin-msft]

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.

[brankocareer]

Hi Charlotte,

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

[brankocareer]

Hi @v-zhangtin-msft ,

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

[brankocareer]

Hi @v-zhangtin-msft 

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

[v-zhangtin-msft]

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.

[brankocareer]

Hi @v-zhangtin-msft ,

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

[brankocareer]

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

[v-zhangtin-msft · Accepted solution]

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.