Forum Discussion

kotarosai's avatar
kotarosai
Icon for Helper II rankHelper II
5 years ago
Solved

DISTINCTCOUNT with multiple filter criteria

Hello, I am having an issue with a measure / formula. One works as intended when using an OR logic but does not work as intended with an AND. Both formulas are below and the measure is shown in the matrix table. Any support is appreciated, thanks!

 

WORKS:

 

Retained Accounts Past QTR = CALCULATE(
                        DISTINCTCOUNT('Sales Result'[Account]),
                        FILTER('Sales Result',[Accounts Ordered Past QTR] > 0 || [Accounts Ordered 2 QTRs Ago] > 0))

 

 

DOES NOT WORK:

 

Retained Accounts Past QTR = CALCULATE(
                        DISTINCTCOUNT('Sales Result'[Account]),
                        FILTER('Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))

 

 

Accounts Ordered Past QTR = CALCULATE(
                        DISTINCTCOUNT('Sales Result'[Account]),
                        FILTER('Sales Result','Sales Result'[Sales Past QTR] > 0))
Accounts Ordered 2 QTRs Ago = CALCULATE(
                        DISTINCTCOUNT('Sales Result'[Account]),
                        FILTER('Sales Result','Sales Result'[Sales 2 QTRs Ago] > 0))
Sales Past QTR = 
VAR CurrentDate = [Latest Date Loaded]
RETURN
CALCULATE(
    SUM('Sales Result'[Net_Sales_Units__c]),
    FILTER(
        ALL('Date Table'),
            AND('Date Table'[Date]>=IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-1)*3+1,1),
                                                Date(1900,1,1)
                                    ),
                'Date Table'[Date]<IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)+0)*3+1,1),
                                            Date(1900,1,1)
                                    )
             )
        )
)
Sales 2 QTRs Ago = 
VAR CurrentDate = [Latest Date Loaded]
RETURN
CALCULATE(
    SUM('Sales Result'[Net_Sales_Units__c]),
    FILTER(
        ALL('Date Table'),
            AND('Date Table'[Date]>=IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-2)*3+1,1),
                                                Date(1900,1,1)
                                    ),
                'Date Table'[Date]<IF(MONTH(CurrentDate)<13,Date(Year(CurrentDate),(INT((Month(CurrentDate)-1)/3)-1)*3+1,1),
                                            Date(1900,1,1)
                                    )
             )
        )
)

 

 

  • kotarosai , Try like and check

     

    Retained Accounts Past QTR = Countrows(
    filter(values('Sales Result'[Account]),'Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))

2 Replies

  • kotarosai , Try like and check

     

    Retained Accounts Past QTR = Countrows(
    filter(values('Sales Result'[Account]),'Sales Result',[Accounts Ordered Past QTR] > 0 && [Accounts Ordered 2 QTRs Ago] > 0))