Calculate Difference Between Month for Quantity with Filters

[XuanNguyen]

Hi, 

I want to calculate the month-over-month difference in quantity while FILTERING based on the same platformID and ShopID.

Example: 
row1 in table: 
P001-S001 - month 1 is 726 

P001-S001 - month 2 = month 2-month 1 = 769-726 = 43

....

Please help me. Thanks

[AntrikshSharma · Accepted solution]

@XuanNguyen Try this:

Diff w OFFSET = 
VAR CurrentQuantity = 
    'Table'[Quantity]
VAR PreviousQuantity = 
    OFFSET ( 
        -1,
        ALL ( 
            'Table'[ProductID], 
            'Table'[PlatformID], 
            'Table'[ShopID], 
            'Table'[CrawlMonth], 
            'Table'[Quantity] 
        ),
        ORDERBY ( 'Table'[CrawlMonth], ASC ),
        PARTITIONBY ( 'Table'[ProductID], 'Table'[PlatformID], 'Table'[ShopID] )    
    )
VAR PreviousQuantityValue = 
    SELECTCOLUMNS ( PreviousQuantity, 'Table'[Quantity] )
VAR Result = 
    CurrentQuantity - PreviousQuantityValue
RETURN 
    Result

[AntrikshSharma · Accepted solution]

@XuanNguyen Try this:

Diff w OFFSET = 
VAR CurrentQuantity = 
    'Table'[Quantity]
VAR PreviousQuantity = 
    OFFSET ( 
        -1,
        ALL ( 
            'Table'[ProductID], 
            'Table'[PlatformID], 
            'Table'[ShopID], 
            'Table'[CrawlMonth], 
            'Table'[Quantity] 
        ),
        ORDERBY ( 'Table'[CrawlMonth], ASC ),
        PARTITIONBY ( 'Table'[ProductID], 'Table'[PlatformID], 'Table'[ShopID] )    
    )
VAR PreviousQuantityValue = 
    SELECTCOLUMNS ( PreviousQuantity, 'Table'[Quantity] )
VAR Result = 
    CurrentQuantity - PreviousQuantityValue
RETURN 
    Result

[Alex87]

can we share the data in a way we can copy it ? 

[XuanNguyen]

https://docs.google.com/spreadsheets/d/1UuUASPU-JqQ5knkoDtUvdC3m-yYcZXHs/edit?usp=sharing&ouid=10199...

Here you are.

(You can download it, and just need to use data with filter storage = 256gb)

[Alex87 · Accepted solution]

Hello @XuanNguyen ,

In order to achieve your purpose, you need to create a calculated column with the following DAX (replace the name of the table MoM with your own table name)

VAR CurrentQuantity = 'MoM'[Quantity]
VAR PreviousMonthQuantity = 
    CALCULATE(
        MAX('MoM'[Quantity]),  
        FILTER(
            'MoM',
            'MoM'[ProductID] = EARLIER('MoM'[ProductID]) &&
            'MoM'[PlatformID] = EARLIER('MoM'[PlatformID]) &&
            'MoM'[ShopID] = EARLIER('MoM'[ShopID]) &&
            'MoM'[CrawlMonth] = EARLIER('MoM'[CrawlMonth]) - 1
        )
    )
RETURN
IF(
    ISBLANK(PreviousMonthQuantity),
    CurrentQuantity,
    CurrentQuantity - PreviousMonthQuantity
)

 Best regards,

Alex