Forum Discussion

nathov's avatar
nathov
Regular Visitor
7 years ago
Solved

DAX with AND and OR

I need to calculate de retention rate of sales and for that is necessary to compare who bought 4 months before the month that I selected with the sales who happen in three in the last months (including the month selected).

So, if I select june, I need to see who bought in march and repeat the pruchase in april ou may ou june.

 

I created this dax solution but it isn't working. The return of this is always empty.

Can someone please help?

 

Retentation Rate =
VAR month = SELECTEDVALUE(Date[Month])
VAR month2 =
if(Month-1=0;
12;
Month-1
)
VAR month3 =
if(Month-2=-1;
11;
if(Month-1=0;
12;
Month-2
)
)
VAR month4 =
if(Month-3=-2;
10;
if(Month-2=-1;
11;
if(Month-1=0;
12;
Month-3
)
)
)
Return
(CALCULATE(
COUNT(Sales[IDNumber]);
ALL(Date);
AND(Date[Month]=month4; 
OR(Date[Month]=month3; 
OR(Date[Month]=month2; 
Date[Month]=month)
)
)
)
/
CALCULATE(
COUNT(Sales[IDNumber]);
ALL(Date);
Date[Month]=month4
)
)
*100

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    nathov ,

     

    Modify your measure like pattern below and check if this issue persists. If the issue persists, please share sample data.

    Retentation Rate =
VAR month =
    SELECTEDVALUE ( Date[Month] )
VAR month2 =
    IF ( Month - 1 = 0; 12; Month - 1 )
VAR month3 =
    IF ( Month - 2 = -1; 11; IF ( Month - 1 = 0; 12; Month - 2 ) )
VAR month4 =
    IF (
        Month - 3 = -2;
        10;
        IF ( Month - 2 = -1; 11; IF ( Month - 1 = 0; 12; Month - 3 ) )
    )
RETURN
    (
        CALCULATE (
            COUNT ( Sales[IDNumber] );
            ALL ( Date );
            FILTER (
                Date;
                Date[Month] = month4
                    && ( Date[Month] = month3
                    || Date[Month] = month2
                    || Date[Month] = month )
            )
        )
            / CALCULATE (
                COUNT ( Sales[IDNumber] );
                ALL ( Date );
                FILTER ( Date; Date[Month] = month4 )
            )
    ) * 100

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    nathov ,

     

    Modify your measure like pattern below and check if this issue persists. If the issue persists, please share sample data.

    Retentation Rate =
VAR month =
    SELECTEDVALUE ( Date[Month] )
VAR month2 =
    IF ( Month - 1 = 0; 12; Month - 1 )
VAR month3 =
    IF ( Month - 2 = -1; 11; IF ( Month - 1 = 0; 12; Month - 2 ) )
VAR month4 =
    IF (
        Month - 3 = -2;
        10;
        IF ( Month - 2 = -1; 11; IF ( Month - 1 = 0; 12; Month - 3 ) )
    )
RETURN
    (
        CALCULATE (
            COUNT ( Sales[IDNumber] );
            ALL ( Date );
            FILTER (
                Date;
                Date[Month] = month4
                    && ( Date[Month] = month3
                    || Date[Month] = month2
                    || Date[Month] = month )
            )
        )
            / CALCULATE (
                COUNT ( Sales[IDNumber] );
                ALL ( Date );
                FILTER ( Date; Date[Month] = month4 )
            )
    ) * 100

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.