Forum Discussion

Trying2Excel's avatar
Trying2Excel
Icon for Helper I rankHelper I
1 year ago
Solved

YoY Growth % DAX Formula

I need help with creating a YoY growth rate for the table below. Below I have also provide my YTD reduction formula.

 

 
 
YTD Total Complete Reductions =
VAR _currentYear =
    YEAR ( MAX ( 'Historical Reductions Table'[End of Month] ) )
VAR _currentMonth =
    MONTH ( MAX ( 'Historical Reductions Table'[End of Month] ) )
RETURN
    IF (
        ISINSCOPE ( 'Historical Reductions Table'[End of Month].[Year]),
        CALCULATE (
            SUM ( 'Historical Reductions Table'[Reduction2] ),
            FILTER (
                ALLSELECTED ('Historical Reductions Table'),
                YEAR ( 'Historical Reductions Table'[End of Month]) = _currentYear
                    && MONTH ( 'Historical Reductions Table'[End of Month] ) <= _currentMonth
            )
        ),
        CALCULATE (
            SUM ( 'Historical Reductions Table'[Reduction2] ),
            FILTER (
                ALLSELECTED ('Historical Reductions Table' ),
                MONTH ( 'Historical Reductions Table'[Date] ) <= _currentMonth
            )
        )
    )

 

  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi Trying2Excel , Thank you for reaching out to the Microsoft Community Forum.

     

    Try below:

    YoY Growth Rate =

    VAR _currentYear = SELECTEDVALUE('Historical Reductions Table'[End of Month].[Year])

    VAR _previousYear = _currentYear - 1

    VAR _currentMonth = MONTH(MAX('Historical Reductions Table'[End of Month]))

     

    VAR _currentYearReduction =

        CALCULATE(

            SUM('Historical Reductions Table'[Reduction2]),

            FILTER(

                ALLSELECTED('Historical Reductions Table'),

                YEAR('Historical Reductions Table'[End of Month]) = _currentYear

                && MONTH('Historical Reductions Table'[End of Month]) <= _currentMonth

            )

        )

     

    VAR _previousYearReduction =

        CALCULATE(

            SUM('Historical Reductions Table'[Reduction2]),

            FILTER(

                ALLSELECTED('Historical Reductions Table'),

                YEAR('Historical Reductions Table'[End of Month]) = _previousYear

                && MONTH('Historical Reductions Table'[End of Month]) <= _currentMonth

            )

        )

     

    RETURN

        IF(

            ISBLANK(_previousYearReduction) || _previousYearReduction = 0,

            BLANK(),

            DIVIDE(_currentYearReduction - _previousYearReduction, _previousYearReduction)

        )

     

    If your End of Month column has a date hierarchy, you can replace YEAR('Historical Reductions Table'[End of Month]) with 'Historical Reductions Table'[End of Month].[Year]' and similarly for MONTH

     

    If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
    Thank you.

5 Replies

  • Pow3Range's avatar
    Pow3Range
    Frequent Visitor
    1 year ago

    YoY Growth % =
    VAR _currentYear =
    YEAR ( MAX ( 'Historical Reductions Table'[End of Month] ) )
    VAR _currentMonth =
    MONTH ( MAX ( 'Historical Reductions Table'[End of Month] ) )

    VAR _currentYTD =
    CALCULATE (
    SUM ( 'Historical Reductions Table'[Reduction2] ),
    FILTER (
    ALLSELECTED ( 'Historical Reductions Table' ),
    YEAR ( 'Historical Reductions Table'[End of Month] ) = _currentYear
    && MONTH ( 'Historical Reductions Table'[End of Month] ) <= _currentMonth
    )
    )

    VAR _previousYTD =
    CALCULATE (
    SUM ( 'Historical Reductions Table'[Reduction2] ),
    FILTER (
    ALLSELECTED ( 'Historical Reductions Table' ),
    YEAR ( 'Historical Reductions Table'[End of Month] ) = _currentYear - 1
    && MONTH ( 'Historical Reductions Table'[End of Month] ) <= _currentMonth
    )
    )

    RETURN
    IF (
    NOT ISBLANK(_previousYTD),
    (_currentYTD - _previousYTD) / _previousYTD,
    BLANK()
    )

    Modified your DAX Can you try this one Trying2Excel   || And if possible can you share your PBIX File?
    If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

    • Trying2Excel's avatar
      Trying2Excel
      Icon for Helper I rankHelper I
      1 year ago

      Thank you for the quick response! The formula provided doesnt seem to be recognizing the year in the table.

       

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Trying2Excel , Thank you for reaching out to the Microsoft Community Forum.

     

    Try below:

    YoY Growth Rate =

    VAR _currentYear = SELECTEDVALUE('Historical Reductions Table'[End of Month].[Year])

    VAR _previousYear = _currentYear - 1

    VAR _currentMonth = MONTH(MAX('Historical Reductions Table'[End of Month]))

     

    VAR _currentYearReduction =

        CALCULATE(

            SUM('Historical Reductions Table'[Reduction2]),

            FILTER(

                ALLSELECTED('Historical Reductions Table'),

                YEAR('Historical Reductions Table'[End of Month]) = _currentYear

                && MONTH('Historical Reductions Table'[End of Month]) <= _currentMonth

            )

        )

     

    VAR _previousYearReduction =

        CALCULATE(

            SUM('Historical Reductions Table'[Reduction2]),

            FILTER(

                ALLSELECTED('Historical Reductions Table'),

                YEAR('Historical Reductions Table'[End of Month]) = _previousYear

                && MONTH('Historical Reductions Table'[End of Month]) <= _currentMonth

            )

        )

     

    RETURN

        IF(

            ISBLANK(_previousYearReduction) || _previousYearReduction = 0,

            BLANK(),

            DIVIDE(_currentYearReduction - _previousYearReduction, _previousYearReduction)

        )

     

    If your End of Month column has a date hierarchy, you can replace YEAR('Historical Reductions Table'[End of Month]) with 'Historical Reductions Table'[End of Month].[Year]' and similarly for MONTH

     

    If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
    Thank you.