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 

 

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)))
      • Anonymous's avatar
        Anonymous
        Not applicable
        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.