Consecutive Months on last 12 Months

[EuniceFleurs]

Hi! I need help with DAX formula. I have some rules that I need to cover:

• 20% discount: if the customer made a purchase in the last 12 months and made a purchase 5 months in a row
• 5% discount: if the customer made a purchase in the last 12 months and made a purchase 3 differents months (regarless of whether they are consecutive or not)

hope you can help me, I couldn't find the way to validate consecutive months with a purchase.

regards.

[EuniceFleurs]

This is an example. Date columns, customer name  and amount. I need to create "discount" column following the rules. I marked each client in a different color. I added a column where I show how purchases should be read, from the first purchase to the third (5% discount) and from the first to the fifth consecutive purchase (20% discount). Hope this is helpful and hope you can help me to solve this.

Regards. 

Year	Month	Customer	Amount	Discount
2018	1	Fred	500	0%		1st purchase
2018	2
2018	3
2018	4
2018	5
2018	6	Jorch	399	0%		1st purchase
2018	7
2018	8	Fred	400	0%		2nd purchase
2018	9	Fred	800	5%		3rd purchase
2018	10	Fred	900	5%		3rd purchase
2018	11	Fred	500	5%		3rd purchase
2018	12	Fred	300	20%		5 in a row
2019	1	Jorch	500	0%		2nd purchase
2019	2
2019	3	Jorch	400	5%		3rd purchase
2019	4
2019	5
2019	6	Jorch	500	5%		3rd purchase
2019	7
2019	8
2019	9	Jorch	600	5%		3rd purchase

[CNENFRNL]

@EuniceFleurs To test 5 months in a row in DAX is really a head-scratching problem, isn't it? @Greg_Deckler

Fortunately, Excel, our old good pal comes in rescue. A solution with Excel fomulas is attached. Just for fun! 😎

[Greg_Deckler]

@CNENFRNL I guess I have to say, not really?? It seems rather straight-forward. It's the __5Distinct variable. If you have exactly 5 months of data then you're just looking for 5 distinct values for the month column.

@EuniceFleurs I attached a PBIX with a DAX solution to the issue at hand. Attached below sig, you want Table (9). Let me know if it works for you, essentially these three columns:

Date = DATE([Year],[Month],1)

Purchase # in Last 12 Months = 
    VAR __Date = [Date]
    VAR __Dates = { [Date] }
    VAR __MinDate = DATE(YEAR(__Date)-1,MONTH(__Date),DAY(__Date))
    VAR __5Date = EOMONTH(__Date,-6)+1
    VAR __Count = COUNTROWS(FILTER('Table (9)',[Date]>=__MinDate && [Date]<=__Date && [Customer] = EARLIER([Customer])))
    VAR __5Count = COUNTROWS(FILTER('Table (9)',[Date]>=__5Date && [Date]<=__Date && [Customer] = EARLIER([Customer])))
    VAR __5Distinct = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table (9)',[Date]>=__5Date && [Date]<=__Date && [Customer] = EARLIER([Customer])),"__Month",[Month])))
RETURN
    SWITCH(TRUE(),
        [Customer] = "",BLANK(),
        __5Count >= 5 && __5Distinct=5,"5 in a row",
        __Count>=3,"3rd purchase",
        __Count=2,"2nd purchase",
        "1st purchase"
    )

Discount = 
    SWITCH(TRUE(),
        [Purchase # in Last 12 Months] = "3rd purchase",.05,
        [Purchase # in Last 12 Months] = "5 in a row",.2,
        [Customer]="",BLANK(),
        0
    )

@ me if problems

[EuniceFleurs]

Don't know how to attach an excel file, but here is a google sheet link with the same dataset

Example Data 

[Anonymous]

DAX can't be written in vacuum. You have to describe your model. And then give us some good examples of what it really is you want. Otherwise, it's not possible to help.

[Greg_Deckler]

@EuniceFleurs Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.