Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! I need help with DAX formula. I have some rules that I need to cover:
hope you can help me, I couldn't find the way to validate consecutive months with a purchase.
regards.
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 |
@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! 😎
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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
Don't know how to attach an excel file, but here is a google sheet link with the same dataset
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |