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/ID Value  June2...
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.

rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
2 years ago

Brewster0101 

 

I think this is what you are looking for - link

 

Please let me know if this is not what you want.

  • 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.