Frequency last months

[Anonymous]

Hi,

I have a table with these columns:

Costumer | Date | Month Number | Sale

I would like to select an interval of date (slicer) and bring this number:

How many costumers have purchased at least 1 product in 3 differents months in that selected time interval 

PS: sorry for my english, i'm brazilian

[amitchandak]

@Anonymous , Try a measure like

Count(filter(summarize(Table, Table[Costumer], Table[Month Number], "_1", DistinctCount(Table[Month Number])), [_1] >=3),[Customer])

[Anonymous]

What do you mean with "_1"? 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you need to create a what-if parameter as slicer.

Take what-if parameter value as the parameter of month interval in the formula.

Measure = COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Costumer],
"count_month",  CALCULATE (
    DISTINCTCOUNT('Table'[Year month]),
    DATESINPERIOD (
        'Table'[Date],
        MAX ( 'Table'[Date]),
        - 'Month interval'[Month interval Value],
        MONTH
    )
)
),
[count_month]>=3
)
)

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