DAX - Measure for customers who never bought, but started buying in the last 2 months

[Diego_Vialle]

Good afternoon, I need some help. I need to create an invoice count measure for customers who have never purchased and have purchased in the last two months. I have the two measurements below:

Bimonthly order frequency = CALCULATE(DISTINCTCOUNT(SBOPRODMS[Customer]),and(SBOPRODMS[invoice date]>=TODAY()-60,SBOPRODMS[document]="outgoing invoice"))

Total customers = CALCULATE(DISTINCTCOUNT(SBOPRODMS[Customer]),and(SBOPRODMS[invoice date]>=TODAY()-365,SBOPRODMS[document]="outgoing invoice"))

[tamerj1 · Accepted solution]

Hi @Diego_Vialle 

You may try

New Customers =
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            < TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        AND (
            SBOPRODMS[invoice date]
                >= TODAY () - 60,
            SBOPRODMS[document] = "outgoing invoice"
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )

[Diego_Vialle]

Thank you very much friend, the formula worked correctly. They have now requested the invoice value of each customer, the measure for invoice is: NET Value | NF = CALCULATE([NET Value],SBOPRODMS[Document]="Outgoing invoice"). How do I implement this data in the bimonthly customers formula??

[tamerj1]

@Diego_Vialle 

Are you trying to calculate the net value of the customers we have calculated in the previous measure? If so you may try

NET Value | NF =
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            < TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            >= TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
RETURN
    CALCULATE ( [NET Value], EXCEPT ( AllNewCustomers, AllOldCustomers ) )

[Diego_Vialle]

Exactly, but when I insert the measure as a matrix column, it brings customers outside of what we calculated in the previous measure. The columns are in Portuguese but I believe you understand based on the formula.

[tamerj1]

@Diego_Vialle 

This is not correct 

 

[Diego_Vialle]

I used your previous suggestion.

[tamerj1]

@Diego_Vialle 
You are referencing a table! In my measure I am referencing another measure [Net Value]. I am using CALCULATE you are using COUNTROWS!

[Diego_Vialle]

Because it is requesting a table and not measure...

[tamerj1]

@Diego_Vialle 

It is CLACULATE not COUNTROWS

[Diego_Vialle]

Oh sorry, I hadn't seen Calculate. I changed it to Calculate and entered the invoice measure. However, it brings up all the clients, ignoring the previous calculation that we made only for the bimonthly clients.

[tamerj1]

@Diego_Vialle 

But are getting correct values?

you can force the fiter in the filter pane for the previous measure set as "is not blank". Also try to iterate over the table but modt probably you will get wrong results but worth try then we can modify incuding all coulmns in the visual with iteration table

NET Value | NF =
VAR AllOldCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            < TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
VAR AllNewCustomers =
    CALCULATETABLE (
        VALUES ( SBOPRODMS[Customer] ),
        SBOPRODMS[invoice date]
            >= TODAY () - 60,
        SBOPRODMS[document] = "outgoing invoice"
    )
RETURN
    SUMX ( EXCEPT ( AllNewCustomers, AllOldCustomers ), [NET Value]  )

[Diego_Vialle]

The New Bimonthly Customers column does not need to add the values, it is enough to bring the invoice value of each bimonthly customer.