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'm transfering manual Excel report to Power BI and I have one issue for the time being.
Database has 3 tables: Sales, Data(calendar), Customers.
Active Customer, is a customer, which had purchased product:
1. at least in 3 out of 12 last months and
2. at least in 1 out of 3 last month
I though about adding in Customer table column, which will say if customer is Active or not.
Thank you for your support !
Example table in Excel below:
Customer | 1901 | 1902 | 1903 | 1904 | 1905 | 1906 | 1907 | 1908 | 1909 | 1910 | 1911 | 1912 | Purchase in last 12 months >= 3 | Purchase in last 3 months >= 1 | Active | |
A | $ 10 | $ 10 | $ 10 | $ 10 | $ 10 | $ 10 | $ - | $ 10 | $ - | $ - | $ 10 | $ 10 | 9 | 2 | YES | |
B | $ - | $ - | $ - | $ - | $ - | $ - | $ 10 | $ - | $ 10 | $ - | $ - | $ - | 2 | 0 | NO | |
C | $ 10 | $ - | $ - | $ - | $ - | $ 10 | $ - | $ - | $ - | $ - | $ - | $ 10 | 3 | 1 | YES |
Hi @PawelMJ ,
If you already did that column that can help a lot in the measure complexity.
Having that column you only have to use Calculate(count('Customers'[ID]),[Active]="YES")
Let me know if it helped, if so mark as solution.
Best Regards,
Duarte Raminhos
Thank you for your replay.
The problem is, that I can't find any simple solution to make logic behind this colum. So I haven't created it. This is the issue.
BR,
Pawel
Oh alright I dind't get it.
To simplify things you can create both columns like:
3 months = if(now()-90<=[Date],1,0) to assign a flag to every entry.
1 Month = if(now()-30<=[Date],1,0) to assign a flag to every entry.
Then last if:
Active/Inactive = if(and([3 months]=1,[1 Month]=1,"Active","Inactive")
To count the active orders use a measure that goes like:
measure = calculate(count([orderID]),Active/Inactive="Active")
With this logic you're categorizing every order and when you do the visual every customer that counts 0 is inactive and it will only consider the orders you really want.
Let me know if it helped.
Best Regards,
Duarte Raminhos
Simple, but unfortunately, won't work.
With this approach, we have information for today.
If we want to see active customers in last month or 6 month ago, it won't work.
Could you provide a sample of your data model so I can understand what paths you have available?
Best Regards,
Duarte Raminhos
I've created sample of data model. Hope that this will help you to understand the problem.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |