Forum Discussion

JACK__'s avatar
JACK__
Frequent Visitor
2 years ago
Solved

Create a Column in Matrix which does (this week price) - (last week price) for multiple weeks

So no idea where to start with this soltuion.

What matrix I am after:

Week11223344
ItemPriceChangePrice ChangePriceChangePriceChange
123456_5 50615-1
456789_4 3-13041

 

The input data is from excel with columns for Item, Week, Price. I am wanting to calculate the change in price from one week to the next within PowerBI. Is it possible to create a single column which will accommodate this when added to the matrix, for example:
Week 2 Change = (Week 2 Price) - (Week 1 Price)
Week 3 Change = (Week 3 Price) - (Week 2 Price)
?

Appreciate the help, hopefully someone has a solution! 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JACK__ ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a calculated column.

    Change = 
IF (
    [Week]
        = CALCULATE (
            MIN ( 'Table'[Week] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) )
        ),
    BLANK (),
    [Price]
        - CALCULATE (
            SUM ( 'Table'[Price] ),
            FILTER (
                'Table',
                'Table'[Item] = EARLIER ( 'Table'[Item] )
                    && 'Table'[Week]
                        = EARLIER ( 'Table'[Week] ) - 1
            )
        )
)

    (3) 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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JACK__ ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a calculated column.

    Change = 
IF (
    [Week]
        = CALCULATE (
            MIN ( 'Table'[Week] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Item] = EARLIER ( 'Table'[Item] ) )
        ),
    BLANK (),
    [Price]
        - CALCULATE (
            SUM ( 'Table'[Price] ),
            FILTER (
                'Table',
                'Table'[Item] = EARLIER ( 'Table'[Item] )
                    && 'Table'[Week]
                        = EARLIER ( 'Table'[Week] ) - 1
            )
        )
)

    (3) 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. 

    • JACK__'s avatar
      JACK__
      Frequent Visitor
      2 years ago

      Thank you, this works great

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

    yes, that sounds like it is possible. Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.