topic Re: Calculate Difference Between Month for Quantity with Filters in DAX Commands and Tips

Calculate Difference Between Month for Quantity with Filters

XuanNguyen — Fri, 26 Apr 2024 16:21:43 GMT

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

Re: Calculate Difference Between Month for Quantity with Filters

Alex87 — Fri, 26 Apr 2024 18:07:28 GMT

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

Re: Calculate Difference Between Month for Quantity with Filters

XuanNguyen — Sat, 27 Apr 2024 07:11:12 GMT

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

Here you are.

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

Re: Calculate Difference Between Month for Quantity with Filters

AntrikshSharma — Sat, 27 Apr 2024 13:43:50 GMT

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

Re: Calculate Difference Between Month for Quantity with Filters

Alex87 — Sat, 27 Apr 2024 14:22:08 GMT

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