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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks!  ALL function had to be on specific column, not on whole table.  

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