Forum Discussion

JajatiDev's avatar
JajatiDev
Helper II
1 year ago
Solved

YoY Growth Card

Hello,
I have created the measure to calculate yoy growth. 

VAR _CY = [Total Order Volume]
VAR _LY = 
    CALCULATE( 
        [Total Order Volume],
        DATESBETWEEN( 
            Calendar_Table[Date],
            EDATE(MIN(Calendar_Table[Date]), -12),
            EOMONTH(MAX(Consolidated_Order_Book[ReceiveDate]), -12)
        )
    )
VAR _Check = _CY && _LY && HASONEVALUE(Calendar_Table[FY_Year])
VAR _Growth = IF(_Check, DIVIDE(_CY,_LY) -1)

RETURN
    _Growth

 The output is giving me the desired result when presented in a graph or table

However, I wish to take this a step forward by presenting the growth rate for the current year in a card dynamically without applying external filters. which I have failed to achieve because the care is returning blank.

Please advise the changes I need to make in the measure to pick the growth rate for FY-25 only.
The financial year in the calculation begins in April and ends in March.


YoY calculation
  • JajatiDev's avatar
    JajatiDev
    1 year ago

    Thank you for all the valuable suggestions.
    I was able to work it out.
    Included the solution below for everyone's reference.

    Also, sharing the screenshot of the card with the final output

    YoY_OrderFlow% = 
VAR _currentDate = TODAY()
VAR _currentYear = 
        IF( MONTH(_currentDate) >= 4, YEAR(_currentDate) + 1, YEAR( _currentDate))
VAR _currentFY = "FY-" & _currentYear
RETURN 
CALCULATE(
    VAR _cy = [TotalOrderedQuantity]
    VAR _ly_startDate = 
        EDATE( MIN( Calendar_Table[Date]), -12)
    VAR _ly_endDate = 
        EOMONTH( MAX( OrderData[ReceiveDate] ), -12)
    VAR _ly = 
        CALCULATE( 
            [TotalOrderedQuantity],
            DATESBETWEEN( 
                Calendar_Table[Date],
                _ly_startDate,
                _ly_endDate
            ),
            REMOVEFILTERS( Calendar_Table[FY_Year] )
        )
    VAR isValidForGrowth = NOT ISBLANK( _cy ) && NOT ISBLANK( _ly ) && _ly <> 0
    VAR _growth = IF( isValidForGrowth, DIVIDE( _cy, _ly) - 1)
    RETURN
        _growth,
        FILTER( 
            ALL( Calendar_Table[FY_Year] ),
            Calendar_Table[FY_Year] = _currentFY
        )
)

     

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    JajatiDev 

    If you need to dynamically determine the current financial year based on today's date, you can use the following approach:

    DAX
    VAR CurrentDate = TODAY()
    VAR CurrentFY =
    IF(
    MONTH(CurrentDate) >= 4,
    YEAR(CurrentDate) & "/" & YEAR(CurrentDate) + 1,
    YEAR(CurrentDate) - 1 & "/" & YEAR(CurrentDate)
    )
    VAR PreviousFY =
    IF(
    MONTH(CurrentDate) >= 4,
    YEAR(CurrentDate) - 1 & "/" & YEAR(CurrentDate),
    YEAR(CurrentDate) - 2 & "/" & YEAR(CurrentDate) - 1
    )
    VAR _CY =
    CALCULATE(
    [Total Order Volume],
    FILTER(
    Calendar_Table,
    Calendar_Table[FY_Year] = CurrentFY
    )
    )
    VAR _LY =
    CALCULATE(
    [Total Order Volume],
    FILTER(
    Calendar_Table,
    Calendar_Table[FY_Year] = PreviousFY
    )
    )
    VAR _Check = _CY && _LY
    VAR _Growth = IF(_Check, DIVIDE(_CY, _LY) - 1)

    RETURN
    _Growth

    • JajatiDev's avatar
      JajatiDev
      Helper II
      1 year ago

      bhanu_gautam 

      Thanks!

      I tried your measure; however, the output is not as desired.
      I need the measure to be dynamic to project the current year growth/decline only. I'm currently achieving the output by applying the year filter to the visual.
      I dont want to put any filters. The dax measure should take care of it. 

       

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi JajatiDev ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    YOY Growth Measure:

    YoY Growth Current FY :=
    VAR _MaxFY = MAX(Calendar_Table[FY_Year]) or use MAXX(ALL(Calendar_Table), Calendar_Table[FY_Year])
    VAR _CY =
    CALCULATE(
    [Total Order Volume],
    Calendar_Table[FY_Year] = _MaxFY
    )
    VAR _LY =
    CALCULATE(
    [Total Order Volume],
    Calendar_Table[FY_Year] = _MaxFY - 1
    )
    VAR _HasData = NOT(ISBLANK(_CY)) && NOT(ISBLANK(_LY))
    RETURN
    IF(_HasData, DIVIDE(_CY, _LY) - 1)

     

    Please refer below community threads.

    Solved: Re: MTD & %YOY in Card Visual - Microsoft Fabric Community

    Solved: How to display year-on-year growth rate - Microsoft Fabric Community

    Solved: Need to calculate Year-over-Year Growth for "year"... - Microsoft Fabric Community

     


    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

     

    • v-dineshya's avatar
      v-dineshya
      Community Support
      1 year ago

      Hi JajatiDev ,

      If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

      Thank you

  • JajatiDev's avatar
    JajatiDev
    Helper II
    1 year ago

    Thank you for all the valuable suggestions.
    I was able to work it out.
    Included the solution below for everyone's reference.

    Also, sharing the screenshot of the card with the final output

    YoY_OrderFlow% = 
VAR _currentDate = TODAY()
VAR _currentYear = 
        IF( MONTH(_currentDate) >= 4, YEAR(_currentDate) + 1, YEAR( _currentDate))
VAR _currentFY = "FY-" & _currentYear
RETURN 
CALCULATE(
    VAR _cy = [TotalOrderedQuantity]
    VAR _ly_startDate = 
        EDATE( MIN( Calendar_Table[Date]), -12)
    VAR _ly_endDate = 
        EOMONTH( MAX( OrderData[ReceiveDate] ), -12)
    VAR _ly = 
        CALCULATE( 
            [TotalOrderedQuantity],
            DATESBETWEEN( 
                Calendar_Table[Date],
                _ly_startDate,
                _ly_endDate
            ),
            REMOVEFILTERS( Calendar_Table[FY_Year] )
        )
    VAR isValidForGrowth = NOT ISBLANK( _cy ) && NOT ISBLANK( _ly ) && _ly <> 0
    VAR _growth = IF( isValidForGrowth, DIVIDE( _cy, _ly) - 1)
    RETURN
        _growth,
        FILTER( 
            ALL( Calendar_Table[FY_Year] ),
            Calendar_Table[FY_Year] = _currentFY
        )
)