Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
brankocareer
Helper I
Helper I

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. 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?

brankocareer_0-1721315074315.png
 
 
I am expecting like this:
brankocareer_1-1721315333214.png

 

 

 



 



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 127,958,388.48
23 Jul 269,359,469.14
23 Aug 0974,230,640.89
23 Aug 2310,730,182.02
23 Sep 066,949,148.62
23 Sep 203,506,014.89
23 Oct 045,868,948.36
23 Oct 185,567,986.30
23 Nov 019,079,104.45
23 Nov 159,319,191.34
23 Nov 297,651,828.76
23 Dec 1314,052,009.68
24 Jan 1019,754,328.61
24 Jan 2410,528,337.85
24 Feb 0721,582,909.32
24 Mar 0612,226,226.63
24 Mar 2058,716,497.92
24 Apr 0323,040,685.34
24 Apr 1713,531,800.86
24 May 0114,780,998.79
24 May 1513,194,504.00
24 May 297,600,276.05
1 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))

vzhangtinmsft_0-1722498207182.png

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.

View solution in original post

10 REPLIES 10
brankocareer
Helper I
Helper I

Hi Charlotte,

 

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

Hi, @brankocareer 

 

You can try the following methods. 

vzhangtinmsft_0-1721714466142.png

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
Community Support
Community Support

Hi, @brankocareer 

 

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

Charlottezmsft_0-1721355015940.png

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

Charlottezmsft_1-1721355066021.png

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.

 

 

Hi Charlotte,

 

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

brankocareer_0-1721421310558.png

 

 

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? 

brankocareer_0-1721833428426.png

Thank you!

Branko

 

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
        )
    )
brankocareer_1-1721835722054.png

Could you please help me out?

Thank you!

Branko

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

vzhangtinmsft_0-1721874886146.png

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.

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

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

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

vzhangtinmsft_0-1722498207182.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors