topic Re: DAX - Measure for customers who never bought, but started buying in the last 2 months in DAX Commands and Tips

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

Diego_Vialle — Mon, 04 Apr 2022 20:18:31 GMT

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

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

tamerj1 — Mon, 04 Apr 2022 21:05:11 GMT

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

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

Diego_Vialle — Mon, 11 Apr 2022 19:16:25 GMT

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

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

tamerj1 — Mon, 11 Apr 2022 19:46:07 GMT

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

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

Diego_Vialle — Mon, 11 Apr 2022 20:20:51 GMT

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.

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

tamerj1 — Mon, 11 Apr 2022 21:02:20 GMT

Diego_Vialle

This is not correct

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

Diego_Vialle — Tue, 12 Apr 2022 11:45:40 GMT

I used your previous suggestion.

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

tamerj1 — Tue, 12 Apr 2022 11:51:45 GMT

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!

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

Diego_Vialle — Tue, 12 Apr 2022 11:56:15 GMT

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

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

tamerj1 — Tue, 12 Apr 2022 12:10:15 GMT

Diego_Vialle

It is CLACULATE not COUNTROWS

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

Diego_Vialle — Tue, 12 Apr 2022 12:48:36 GMT

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.

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

tamerj1 — Tue, 12 Apr 2022 13:13:29 GMT

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

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

Diego_Vialle — Tue, 12 Apr 2022 18:14:07 GMT

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