Forum Discussion

brankocareer's avatar
brankocareer
Helper I
2 years ago
Solved

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?

 
 
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 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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      brankocareer
      Helper I
      2 years ago

      Hi Charlotte,

       

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

       

       

      • brankocareer's avatar
        brankocareer
        Helper I
        2 years ago

        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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.