Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Year Over Year change

Hello, 

I want to see the year over year change in the $/Kg calculation

 

I have the measure [k/g]= sum(table1;[$])/sum(Table1;[kg])

 

my data base looks like the first picture, and I want to see the measure like the second table in power bi

 

Please someone help me 

 

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    You can try to use below measure if it works for your scenario:

    YOY $/Kg =
VAR currYear =
    YEAR ( MAX ( Table1[Date] ) )
VAR currSKG =
    CALCULATE (
        SUM ( table1[$] ) / SUM ( Table1[kg] ),
        FILTER ( ALLSELECTED ( Table1 ), YEAR ( Table1[Date] ) = currYear ),
        VALUES ( Table1[Item] )
    )
VAR prevSKG =
    CALCULATE (
        SUM ( table1[$] ) / SUM ( Table1[kg] ),
        FILTER ( ALLSELECTED ( Table1 ), YEAR ( Table1[Date] ) = currYear - 1 ),
        VALUES ( Table1[Item] )
    )
RETURN
    IF ( prevSKG <> BLANK (), DIVIDE ( currSKG - prevSKG, prevSKG, -1 ), 1 )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    You can try to use below measure if it works for your scenario:

    YOY $/Kg =
VAR currYear =
    YEAR ( MAX ( Table1[Date] ) )
VAR currSKG =
    CALCULATE (
        SUM ( table1[$] ) / SUM ( Table1[kg] ),
        FILTER ( ALLSELECTED ( Table1 ), YEAR ( Table1[Date] ) = currYear ),
        VALUES ( Table1[Item] )
    )
VAR prevSKG =
    CALCULATE (
        SUM ( table1[$] ) / SUM ( Table1[kg] ),
        FILTER ( ALLSELECTED ( Table1 ), YEAR ( Table1[Date] ) = currYear - 1 ),
        VALUES ( Table1[Item] )
    )
RETURN
    IF ( prevSKG <> BLANK (), DIVIDE ( currSKG - prevSKG, prevSKG, -1 ), 1 )

    Regards,

    Xiaoxin Sheng