Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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:
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 |
Solved! Go to 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.
Hi Charlotte,
I don't get you, could you share your pbix file?
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.
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.
Hi Charlotte,
Your method works at year level. If you add month, it won't work.
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
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.
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))
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 :
Sorry, I pasted the wrong measure, current my measure is :
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
14 |
User | Count |
---|---|
44 | |
36 | |
24 | |
24 | |
22 |