Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 expect...
  • Fowmy's avatar
    Fowmy
    5 years ago

    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
        )
    )
Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

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
        )
    )