Forum Discussion

Brewster0101's avatar
Brewster0101
New Member
2 years ago
Solved

Inventory Month on Month comparison

Hello, 

I'm trying to create in Power BI a month on month comparison of stock value by SKU/ID

Struggling to find a solution. 

 

My data looks like this, 

Month (Date)SKU/IDValue 
June23 10.26
July23 10.36
Aug23 10.10
June23 20.50
July23 20.75
June23 31.20
Aug2330.50

 

The output I'm trying to get to is a table of the month to month change so I can by month see the biggest varience by value and percentage change.

 

Many thanks in advance

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Brewster0101 ,

     

    I think you can try code as below to create measures.

    Previous Data by SKU = 
VAR _PREVIOUSVALUE =
    CALCULATE (
        MAX ( 'Table'[Month (Date)] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SKU/ID] ),
            'Table'[Month (Date)] < MAX ( 'Table'[Month (Date)] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value ] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SKU/ID] ),
            'Table'[Month (Date)] = _PREVIOUSVALUE
        )
    )
    Diff = CALCULATE(SUM('Table'[Value ])) - [Previous Data by SKU]
    Change Percentage = 
DIVIDE([Diff],[Previous Data by SKU])

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

4 Replies

    • Brewster0101's avatar
      Brewster0101
      New Member
      2 years ago

      Many thanks for reply. 

      I've tried a few of these examples, but they all have only two sets of data to consider,  Date and sales 

      I need to also consider a third data set - SKU/PN 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Brewster0101 ,

         

        I think you can try code as below to create measures.

        Previous Data by SKU = 
VAR _PREVIOUSVALUE =
    CALCULATE (
        MAX ( 'Table'[Month (Date)] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SKU/ID] ),
            'Table'[Month (Date)] < MAX ( 'Table'[Month (Date)] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value ] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SKU/ID] ),
            'Table'[Month (Date)] = _PREVIOUSVALUE
        )
    )
        Diff = CALCULATE(SUM('Table'[Value ])) - [Previous Data by SKU]
        Change Percentage = 
DIVIDE([Diff],[Previous Data by SKU])

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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