Forum Discussion
Anonymous
5 years agoNot applicable
CALCULATE with FILTER by Row Grouping
I've got this DAX that produces the correct result for the total quantity from 3 months previous. However, when I drag Transaction Type into my table, I still get that total result, but I was expecting to now see that total carved up by Transaction Type. What do I modify to get this to behave appropriately?
Ordered Qty Prev 3 Months Total TEST =
VAR __PREV_MONTH =
EOMONTH (
MAX ( 'Supply_Chain Orders'[Ship Date] ),
-1
)
VAR __PREV_MONTH3 =
EOMONTH (
MAX ( 'Supply_Chain Orders'[Ship Date] ),
-4
)
RETURN
CALCULATE (
SUM ( 'Supply_Chain Orders'[Ordered Quantity] ),
FILTER (
ALL ( 'Supply_Chain Orders' ),
'Supply_Chain Orders'[Ship Date].[Date] > __PREV_MONTH3
&& 'Supply_Chain Orders'[Ship Date].[Date] <= __PREV_MONTH
)
)
Anonymous
Can you try this :Ordered Qty Prev 3 Months Total TEST = VAR __PREV_MONTH = EOMONTH ( MAX ( 'Supply_Chain Orders'[Ship Date] ), -1 ) VAR __PREV_MONTH3 = EOMONTH ( MAX ( 'Supply_Chain Orders'[Ship Date] ), -4 ) RETURN CALCULATE ( SUM ( 'Supply_Chain Orders'[Ordered Quantity] ), FILTER ( ALL ( 'Supply_Chain Orders'[Ship Date].[Date] ), 'Supply_Chain Orders'[Ship Date].[Date] > __PREV_MONTH3 && 'Supply_Chain Orders'[Ship Date].[Date] <= __PREV_MONTH ) )
2 Replies
- AnonymousNot applicable
Thanks! ALL function had to be on specific column, not on whole table.
- Fowmy
Super User
Anonymous
Can you try this :Ordered Qty Prev 3 Months Total TEST = VAR __PREV_MONTH = EOMONTH ( MAX ( 'Supply_Chain Orders'[Ship Date] ), -1 ) VAR __PREV_MONTH3 = EOMONTH ( MAX ( 'Supply_Chain Orders'[Ship Date] ), -4 ) RETURN CALCULATE ( SUM ( 'Supply_Chain Orders'[Ordered Quantity] ), FILTER ( ALL ( 'Supply_Chain Orders'[Ship Date].[Date] ), 'Supply_Chain Orders'[Ship Date].[Date] > __PREV_MONTH3 && 'Supply_Chain Orders'[Ship Date].[Date] <= __PREV_MONTH ) )