Forum Discussion
Customer buying every year
Hi there,
I am new learner and I am stuggling on trying to find out revenue from customers buying products every year.
Could someone help me with my senario please?
For example below table is sales data during 2022- 2024, how can I find out Marie who bought every year and her purchase amout?
| Customer Name | Order Date | Order Qty | Sales Amt |
| Marie | 11/23/2022 | 2 | 100 |
| Marie | 12/28/2022 | 5 | 250 |
| Marie | 02/16/2023 | 4 | 200 |
| Marie | 04/28/2024 | 1 | 50 |
| Julie | 09/18/2023 | 8 | 400 |
| Julie | 11/02/2023 | 3 | 150 |
| Julie | 06/12/2024 | 5 | 250 |
| Julie | 08/22/2024 | 2 | 100 |
| Julie | 11/05/2024 | 7 | 350 |
Thanks in advance.
Hi KimGriso
This is a measure only approach:
Customer Sales with all years = VAR YearsWithDupes = // Build a table of all order dates and customers, with a computed "Year" column SUMMARIZE ( ALL ( 'Table' ), // remove filters, work over entire table 'Table'[Order Date], 'Table'[Customer Name], "Year", YEAR ( 'Table'[Order Date] ) ) VAR AllYears = // Count how many distinct years exist in the whole table COUNTROWS ( GROUPBY ( YearsWithDupes, [Year] ) ) RETURN SUMX ( FILTER ( // Build a per-customer table: one row per customer with sales and year count SUMMARIZECOLUMNS ( 'Table'[Customer Name], "@amount", SUM ( 'Table'[Sales Amt] ), // total sales per customer "@CustomerYears", COUNTROWS ( GROUPBY ( FILTER ( YearsWithDupes, [Customer Name] IN VALUES ( 'Table'[Customer Name] ) // only that customer ), [Year] // group by year ) ) // count distinct years for that customer ), // Keep only customers whose distinct years match the overall distinct years [@CustomerYears] = AllYears ), [@amount] // sum the sales of the qualifying customers )There are other approaches in the attached pbix that requires a dedicated tables and helper columns in the fact table.
Performance difference amount these approaches is neglible with small tables but can be obvious otherwise especially for the approach above.
9 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Who bought every year: = VAR _year = ALL ( 'Calendar'[Year] ) VAR _allyearcount = COUNTROWS ( _year ) VAR _sales = FILTER ( ADDCOLUMNS ( _year, "@salesamount", CALCULATE ( SUM ( sales[Sales Amt] ) ) ), [@salesamount] <> BLANK () ) VAR _count = COUNTROWS ( _sales ) RETURN IF ( _allyearcount == _count, "Yes", "No" )Who bought every year sales amount: = VAR _year = ALL ( 'Calendar'[Year] ) VAR _allyearcount = COUNTROWS ( _year ) VAR _sales = FILTER ( ADDCOLUMNS ( _year, "@salesamount", CALCULATE ( SUM ( sales[Sales Amt] ) ) ), [@salesamount] <> BLANK () ) VAR _count = COUNTROWS ( _sales ) RETURN IF ( _allyearcount == _count, SUM(sales[Sales Amt] ) ) - Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
Hello Mathur,
Thanks for your support but it seems not work at my side as the result shows all customers and revenue.
Like below screenshort, my sales data including 03 years 2015, 2016, 2017 so this customer should not be in the list of customer who buy every year because he only bought 2016, 2017.
- Ashish_Mathur
i do not know what mistake you are committing. As can be seen very clearly, it works fine on my file.
ryan_mayuyou can try to create a year column
Column = year('Table'[Order Date])pls see the attachment belowHi KimGriso ,
I would also take a moment to thank Jihwan_Kim , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Hi KimGriso ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you
Hi KimGriso ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
- danextian
Hi KimGriso
This is a measure only approach:
Customer Sales with all years = VAR YearsWithDupes = // Build a table of all order dates and customers, with a computed "Year" column SUMMARIZE ( ALL ( 'Table' ), // remove filters, work over entire table 'Table'[Order Date], 'Table'[Customer Name], "Year", YEAR ( 'Table'[Order Date] ) ) VAR AllYears = // Count how many distinct years exist in the whole table COUNTROWS ( GROUPBY ( YearsWithDupes, [Year] ) ) RETURN SUMX ( FILTER ( // Build a per-customer table: one row per customer with sales and year count SUMMARIZECOLUMNS ( 'Table'[Customer Name], "@amount", SUM ( 'Table'[Sales Amt] ), // total sales per customer "@CustomerYears", COUNTROWS ( GROUPBY ( FILTER ( YearsWithDupes, [Customer Name] IN VALUES ( 'Table'[Customer Name] ) // only that customer ), [Year] // group by year ) ) // count distinct years for that customer ), // Keep only customers whose distinct years match the overall distinct years [@CustomerYears] = AllYears ), [@amount] // sum the sales of the qualifying customers )There are other approaches in the attached pbix that requires a dedicated tables and helper columns in the fact table.
Performance difference amount these approaches is neglible with small tables but can be obvious otherwise especially for the approach above.
