Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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
can we share the data in a way we can copy it ?
Here you are.
(You can download it, and just need to use data with filter storage = 256gb)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |