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.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

      Hi Anonymous ,

       

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

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