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

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

       

      • brankocareer's avatar
        brankocareer
        Helper I
        2 years ago

        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