Formatting difference between two numbers

[newpbiuser01]

Hello,

I'm trying to use a measure to calculate the difference between last years and this year spend and format it to show the data as thousands, millions etc. 

I'm using the following measure:

Payment YoY = 

VAR PaymentCurrentY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2023"))

VAR PaymentPreviousY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2022"))

VAR YoYAmount =PaymentCurrentY - PaymentPreviousY

RETURN

     SWITCH(TRUE (),

        YoYAmount< 1000 && NOT(ISBLANK(YoYAmount)), FORMAT(YoYAmount,"$0.0"),

        YoYAmount >= 1000 && YoYAmount < 1000000 , CONCATENATE ( FORMAT(YoYAmount / 1000,"$0.0"), "k" ),

        YoYAmount >= 1000000 && YoYAmount < 1000000000 ,  CONCATENATE ( FORMAT(YoYAmount / 1000000,"$0.0"),"M"  ),

        YoYAmount >= 1000000000 && YoYAmount <= 1000000000000 ,  CONCATENATE (FORMAT(YoYAmount/1000000000,"$0.0"), "B" ) )

However, this doesn't seem to work. When I applied the same switch statement for formatting on just this years spend (without subtracting the previous from the current) the formatting works like a charm:

Payment 2023= 

VAR PaymentCurrentY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2023"))

VAR PaymentPreviousY = CALCULATE(SUM('Table1[Amount], FILTER('Table1',[Y] = "2022"))

VAR YoYAmount =PaymentCurrentY

RETURN

     SWITCH(TRUE (),

        YoYAmount< 1000 && NOT(ISBLANK(YoYAmount)), FORMAT(YoYAmount,"$0.0"),

        YoYAmount >= 1000 && YoYAmount < 1000000 , CONCATENATE ( FORMAT(YoYAmount / 1000,"$0.0"), "k" ),

        YoYAmount >= 1000000 && YoYAmount < 1000000000 ,  CONCATENATE ( FORMAT(YoYAmount / 1000000,"$0.0"),"M"  ),

        YoYAmount >= 1000000000 && YoYAmount <= 1000000000000 ,  CONCATENATE (FORMAT(YoYAmount/1000000000,"$0.0"), "B" ) )

 Why does the formatting not work when I try to apply it on the current spend - previous spend? I checked and the YoYAmount is a number, so it should be the same. 

Thank you for your help!

[Wilson_ · Accepted solution]

Hello,

All your YoY values are negative, and therefore are all less than 1,000. The implicit assumption in your SWITCH statement is that YoYAmount will always be positive. Therefore, you need to wrap all your YoYAmount variables in the SWITCH statement in an ABS function.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Ahmedx]

pls see this vidio

https://www.youtube.com/watch?v=b6E5CiVrwHI&t=1064s&ab_channel=PeryTUS-PowerBIHowTo

and

https://www.youtube.com/watch?v=b6E5CiVrwHI&t=1064s&ab_channel=PeryTUS-PowerBIHowTo