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 everyone,
I have a dataset with the following invoices:
Customer | Invoice | Invoice_Date | Year_Invoice | Amount_Excl_VAT | YMD-key |
1 | 1001 | 1-3-2014 | 2014 | 25 | 20140301 |
2 | 1002 | 5-6-2014 | 2014 | 40 | 20140605 |
3 | 1003 | 9-9-2014 | 2014 | 35 | 20140909 |
4 | 1004 | 10-12-2014 | 2014 | 12 | 20141210 |
1 | 1005 | 1-4-2015 | 2015 | 45 | 20150401 |
2 | 1006 | 3-3-2015 | 2015 | 38 | 20150303 |
3 | 1007 | 2-2-2015 | 2015 | 27 | 20150202 |
5 | 1008 | 4-6-2015 | 2015 | 30 | 20150604 |
6 | 1009 | 2-8-2015 | 2015 | 60 | 20150802 |
7 | 1010 | 7-7-2015 | 2015 | 40 | 20150707 |
8 | 1011 | 15-10-2015 | 2015 | 35 | 20151015 |
1 | 1012 | 21-2-2016 | 2016 | 73 | 20160221 |
2 | 1013 | 7-11-2016 | 2016 | 45 | 20161107 |
5 | 1014 | 15-7-2016 | 2016 | 60 | 20160715 |
6 | 1015 | 2-4-2016 | 2016 | 60 | 20160402 |
7 | 1016 | 28-8-2016 | 2016 | 38 | 20160828 |
9 | 1017 | 14-1-2016 | 2016 | 10 | 20160114 |
10 | 1018 | 8-11-2016 | 2016 | 15 | 20161108 |
11 | 1019 | 25-11-2016 | 2016 | 27 | 20161125 |
1 | 1020 | 1-4-2017 | 2017 | 98 | 20170401 |
5 | 1021 | 23-2-2017 | 2017 | 58 | 20170223 |
7 | 1022 | 8-12-2017 | 2017 | 36 | 20171208 |
9 | 1023 | 24-12-2017 | 2017 | 20 | 20171224 |
10 | 1024 | 6-6-2017 | 2017 | 12 | 20170606 |
11 | 1025 | 1-6-2017 | 2017 | 27 | 20170601 |
12 | 1026 | 8-6-2017 | 2017 | 25 | 20170608 |
For each quarter I want to calculate the count of customers who bought in their first year and I also want to count the customers who are not in their first year. For example, at the end of Q4-2014 there are 4 customers who are in their first year.
Can someone help me?
Thanks in advance,
Cor
Solved! Go to Solution.
@Anonymous
For each customer I've calculated the first en last day of the invoices:
Can someone help me with the dax-formulas?
I've tried it with the formula:
# First year =
COUNTROWS(
CALCULATETABLE(VALUES(Invoices[Customer]);
FILTER(Invoices;Invoices[Invoice_Date]-Invoices[First Invoicedate] <= 365)))
but it gave me the result for each quarter and I want the total of the customers of the last 12 months....
hi @Anonymous
What is the end result you want with above sample data?
@Anonymous
Please see if the attached file helps
I have used this MEASURE
FirstYearCustomerCount = VAR selectedyear = SELECTEDVALUE ( Table1[Invoice_Date].[Year] ) VAR CurrentYearCustomer = CALCULATETABLE ( VALUES ( Table1[Customer] ), ALLEXCEPT ( Table1, Table1[Invoice_Date].[Year] ) ) VAR AllPreviousYearsCustomers = CALCULATETABLE ( VALUES ( Table1[Customer] ), FILTER ( ALL ( Table1 ), Table1[Invoice_Date].[Year] < selectedyear ) ) RETURN COUNTROWS ( EXCEPT ( CurrentYearCustomer, AllPreviousYearsCustomers ) )
Hi Zubair,
Thanks for your reply, I really appreciate it!
About the challenge, I had expected a different endresult.
My challenge was to create churn-measures for each quarter, calculated for the last twelve months:
Total Clients LTM =
CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
DATESBETWEEN('Calendar'[Date];
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])));
LASTDATE('Calendar'[Date])))
Now I want the Total Clients LTM separated in 2 parts, the number of 1st year customers and 2nd + year customers.
A part of the correct solution should be:
Total Customers | # First Year | # Second + Year | Note | |
2014-Q1 | 1 | 1 | 0 | |
2014-Q2 | 2 | 2 | 0 | |
2014-Q3 | 3 | 3 | 0 | |
2014-Q4 | 4 | 4 | 0 | |
2015-Q1 | 3 | 3 | 0 | Customer 1 is a lost client |
2015-Q2 | 5 | 3 | 2 | Customers 1 and 2 are in their second year |
Do you understand and can you help me?
Regards,
Cor
The calculation of the customers of the prior period:
Total Clients Prior Period =
COUNTROWS(
CALCULATETABLE(VALUES(Invoices[Customer]);
DATESBETWEEN('Calendar'[Date];
DATEADD(STARTOFQUARTER('Calendar'[Date]);-7;QUARTER);
DATEADD(ENDOFQUARTER('Calendar'[Date]);-4;QUARTER))))
@Anonymous
Try these MEASURE
Please see attached file
# First Year = VAR mydate = MAX ( 'Calendar'[Date] ) VAR AllCustomers = CALCULATETABLE ( VALUES ( Table1[Customer] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) VAR CustomersforOneYear = CALCULATETABLE ( VALUES ( Table1[Customer] ), DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ) ) VAR Customersfor2Year = CALCULATETABLE ( VALUES ( Table1[Customer] ), SAMEPERIODLASTYEAR ( DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ) ) ) RETURN COUNTROWS ( EXCEPT ( CustomersforOneYear, Customersfor2Year ) )
Total Customers = VAR mydate = MAX ( 'Calendar'[Date] ) VAR AllCustomers = CALCULATETABLE ( VALUES ( Table1[Customer] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) VAR CustomersforOneYear = CALCULATETABLE ( VALUES ( Table1[Customer] ), DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ) ) RETURN COUNTROWS ( CustomersforOneYear )
@Anonymous
Thanks for your reply and strange, it wasn't working for my dataset. I've sent you my pbix-file to your hotmail.
Please, can you take a look at the file and tell me what is going wrong?
Thanks in advance,
Regards,
Cor
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |