Forum Discussion

jonclay's avatar
jonclay
Icon for Helper IV rankHelper IV
2 years ago
Solved

Working out the %age increase / decrease year on year

Hi everyone

I'm trying to work out the percentage increase or decrease based on year on year values within a table.

I'm using this DAX Measure to calculate the value of payments from each contact:

Paid (Nett) = CALCULATE(SUM(values[nett]), FILTER(values, values[status]="Paid"))

This is then shown in a Table:


I'd like to achieve a DAX Measure (preferably) that shows the increase / decrease between the various years e.g. that 1983/1984 is a 2000% increased on 1982/1983 and that 1984/1985 is a 99% decreasd on 1983/1984 etc.

Could anyone please tell me the best way to achieve this?

Many thanks
Jon



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jonclay ,

     

    Thanks divyed  for the quick reply. I have some other thoughts to add:

    (1) We can create measures.

    rank =
RANKX (
    SUMMARIZE ( ALLSELECTED ( 'Fin_Yr' ), [Fiscal Year], "nett", [Paid (Nett)] ),
    [Fiscal Year],
    MAX ( 'Fin_Yr'[Fiscal Year] )
)

    Change = var _rank=[rank]+1
var _pre= CALCULATE([Paid (Nett)],FILTER(ALLSELECTED('values'),[rank]=_rank))
var _current=[Paid (Nett)]
RETURN DIVIDE(_current-_pre,_pre)

    (2) Then the result is as follows.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

4 Replies

  • divyed's avatar
    divyed
    Icon for Super User rankSuper User
    2 years ago

    Hello jonclay ,

     

    You can try below dax :

     

    YOY Change =
    VAR CurrentYear = VALUE(LEFT(T_Paid[Fiscal_Year], 4))
    VAR Previous_Year = CurrentYear - 1
    VAR PreviousYearValue =
        CALCULATE(
            SUM(T_Paid[Paid_Nett]),
            FILTER(
                T_Paid,
                VALUE(LEFT(T_Paid[Fiscal_Year], 4)) = Previous_Year
            )
        )
    RETURN
        IF(
            NOT(ISBLANK(PreviousYearValue)),
            (T_Paid[Paid_Nett] - PreviousYearValue) / PreviousYearValue,
            BLANK()
        )
     

     

     

    I hope this helps. Did I answer your query ? Mark this as solution if this has solved your issue.

     

    Cheers

    • jonclay's avatar
      jonclay
      Icon for Helper IV rankHelper IV
      2 years ago

      Hi there divyed 

      Many thanks for your reply.

      The Fiscal Year field is a calculated column on the Fin_Yr table, so I can't seem to get your code to work. I'm using the following code to create the column:

      Fiscal Year = IF(month('Fin_Yr'[Date])<9,year('Fin_Yr'[Date])-1&"/"&year('Fin_Yr'[Date]),year('Fin_Yr'[Date])&"/"&year('Fin_Yr'[Date])+1)

      How could I amend your code to accommodate this?

      Many thanks
      Jon



  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jonclay ,

     

    Thanks divyed  for the quick reply. I have some other thoughts to add:

    (1) We can create measures.

    rank =
RANKX (
    SUMMARIZE ( ALLSELECTED ( 'Fin_Yr' ), [Fiscal Year], "nett", [Paid (Nett)] ),
    [Fiscal Year],
    MAX ( 'Fin_Yr'[Fiscal Year] )
)

    Change = var _rank=[rank]+1
var _pre= CALCULATE([Paid (Nett)],FILTER(ALLSELECTED('values'),[rank]=_rank))
var _current=[Paid (Nett)]
RETURN DIVIDE(_current-_pre,_pre)

    (2) Then the result is as follows.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • jonclay's avatar
    jonclay
    Icon for Helper IV rankHelper IV
    2 years ago

    Thank you, everyone.

    These all work in different ways, so I have a great choice 🙂