Count customers who made at least three order in the last three Months

[rockbredo9]

Hi All, 

I have three tables , one for sales , one for calendar and one for customers. I have one date for all  days because are the sales for a mall center. Orders are the numbers of receipts /sales that a customer made. wth two years of datas.

Sales :

Date	CustomerKey	Orders
01/01/2022	0401002	1
02/01/2022	0401003	1
03/01/2022	0401004	1
04/01/2022	0401005	1
05/01/2022	0401006	1
06/01/2022	0401007	1
07/01/2022	0401008	2
08/01/2022	0401009	1
09/01/2022	0401010	2
10/01/2022	0401005	1
...	..	..

Calendar : 

date	month	year
01/01/2022	1	2022
02/01/2022	1	2022
03/01/2022	1	2022
04/01/2022	1	2022
05/01/2022	1	2022
06/01/2022	1	2022
07/01/2022	1	2022
08/01/2022	1	2022
09/01/2022	1	2022

I want to know the number of customers who made at least three ( >=3 orders) in the last three months.

I previously calculated a measure for the total of Orders

I tried with this :

VAR CustomerswithOrders =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( Sales[CustomerKey] ),
"@NumberofSales", [TotalOrders]
),
ALLEXCEPT ( Sales, Customer ),DATESINPERIOD (Calendar[Date];MAX(Calendar[Date];-3;MONTHS)))

VAR CustomerswithThreeOrders =
FILTER(CustomerswithOrders ; "@NumberofSales" >=3)
VAR Result
COUNTROWS(CustomerswithThreeOrders)
RETURN
Result

The problem is in filtering the time period i think. There is no error when creating the measure. But when I add it to a visual I get the error:  'A table of multiple values was supplied when a single value was expected'

Thanks, 

[v-zhangti · Accepted solution]

Hi, @rockbredo9 

You can try the following methods.
Sample data:

Measure:

Count order = 
CALCULATE (
    SUM ( 'Table'[Order] ),
    FILTER (
        ALL ( 'Table' ),
        [Date] <= TODAY ()
            && [Date] >= TODAY () - 90
            && [Customer ID] = SELECTEDVALUE ( 'Table'[Customer ID] )
    )
)

Count Customer = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]),FILTER(ALL('Table'),[Count order]>=3))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ]

try like:

VAR CustomerswithOrders =

CALCULATETABLE (

ADDCOLUMNS (

VALUES ( Sales[CustomerKey] ),

"@NumberofSales", [TotalOrders]

),

ALLEXCEPT ( Sales, Customer ),DATESINPERIOD (Calendar[Date];MAX(Calendar[Date];-3;MONTH)))

VAR CustomerswithThreeOrders =

FILTER(CustomerswithOrders ; [@NumberofSales]>=3)

VAR Result

COUNTROWS(CustomerswithThreeOrders)

RETURN

Result