Forum Discussion
Customer Analytics with Sales Data set and Dynamic YTD date range
I am developing a PBI dashboard for customer analytics based on a sales data set
My sales data contains Product Sales by Customer by Month Year (date column). Month Year links to a DateTable, where the user can select a date. I am trying to set up a table with customer in the rows. The columns include the following:
- YTD Sales (based on the the date selected by the user)
- Prior Year YTD Sales
- Sales Delta (YTD Sales - Prior Year YTD Sales)
- Sales Delta from New Customers (YTD Sales if the customer had 0 sales in the Prior Year YTD period and nonzero sales in YTD period)
- Sales Delta from Churned Customers (Prior YTD Sales if the customer had nonzero sales in the Prior Year YTD period and zero sales in YTD period)
- Sales Delta from Existing Customers (Sales Delta if the customer had nonzero sales in the Prior Year YTD period and nonzero sales in YTD period)
Here is an example data set:
Customer | Month Year | Product Sales |
A | 2/1/2024 | 3 |
A | 9/1/2024 | 17 |
A | 2/1/2025 | 20 |
B | 10/1/2024 | 14 |
B | 2/1/2025 | 5 |
C | 2/1/2024 | 8 |
Here is the intended output if the user select Mar 2025 as the input:
Customer | Prior Year YTD Sales | Sales Delta from New Customers | Sales Delta from Churned Customers | Sales Delta Existing | YTD Sales | Sales Delta |
A | 3 | 0 | 0 | 17 | 20 | 17 |
B | 0 | 5 | 0 | 0 | 5 | 5 |
C | 8 | 0 | 8 | 0 | 0 | -8 |
Total | 11 | 5 | 8 | 17 | 25 | 14 |
I have been able to calcualte these metrics at the cusotmer level, but cannot figure for the life of me out how to get them to sum up correctly at the Grand Total level, such as in Matrix visual in the example above. I am open to any kind of workarounds if it is impossible to do so with Measures alone
Thank you for providing the updated dashboard. Unfortunately this still doesnt match the desired output - sales detla from churn, existing customers, and new customers does calculate at the total level, but not at the customer level. This is what I'm seeing:
My solution is to do the analysis in Excel. I pull my data into an excel workbook by using the same PowerQueries as I am using in PBI. Then I'm able to add columns to the right of the data which automatically update. I set my input date into a table in Excel with 1 row to reference in calculations. I can then connect my PBI to the Excel workbook to import the analysis and the current period. This allows me to do significantly more flexible analysis while keeping the formulas simple.
Let me know if you have any suggestions to further refine this process
6 Replies
- grazitti_sapna
Super User
Hi kc6859,
It's tricky to build a customer-level YTD delta breakdown that also sums correctly in the matrix total row.
Follow Below Steps:
Let's first define our ranges, create below Measures:
VAR _maxDate = MAX('DateTable'[Date])
VAR _currentYTD =
DATESYTD('DateTable'[Date], "12/31") // adjust fiscal year end if needed
VAR _priorYTD =
DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31")Create a virtual table with YTD and PYTD customers
VAR Customers =
SUMMARIZE(
'Sales',
'Sales'[Customer],
"YTD_Sales", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _currentYTD),
"PYTD_Sales", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _priorYTD)
)Create New Customers Delta measure using below DAX:
Sales Delta from New Customers =
VAR _maxDate = MAX('DateTable'[Date])
VAR _currentYTD = DATESYTD('DateTable'[Date], "12/31")
VAR _priorYTD = DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31")RETURN
SUMX(
ADDCOLUMNS(
VALUES('Sales'[Customer]),
"CurrentYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _currentYTD),
"PriorYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _priorYTD)
),
VAR CY = [CurrentYTD]
VAR PY = [PriorYTD]
RETURN IF(PY = 0 && CY > 0, CY, 0)
)Same for churn customers delta
Sales Delta from Churned Customers =
VAR _maxDate = MAX('DateTable'[Date])
VAR _currentYTD = DATESYTD('DateTable'[Date], "12/31")
VAR _priorYTD = DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31")RETURN
SUMX(
ADDCOLUMNS(
VALUES('Sales'[Customer]),
"CurrentYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _currentYTD),
"PriorYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _priorYTD)
),
VAR CY = [CurrentYTD]
VAR PY = [PriorYTD]
RETURN IF(PY > 0 && CY = 0, -PY, 0)
)And then for existing customers
Sales Delta from Existing Customers =
VAR _maxDate = MAX('DateTable'[Date])
VAR _currentYTD = DATESYTD('DateTable'[Date], "12/31")
VAR _priorYTD = DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31")RETURN
SUMX(
ADDCOLUMNS(
VALUES('Sales'[Customer]),
"CurrentYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _currentYTD),
"PriorYTD", CALCULATE(SUM('Sales'[Product Sales]), 'DateTable'[Date] IN _priorYTD)
),
VAR CY = [CurrentYTD]
VAR PY = [PriorYTD]
RETURN IF(PY > 0 && CY > 0, CY - PY, 0)
)YTD Sales and Prior YTD Sales
YTD Sales = CALCULATE(SUM('Sales'[Product Sales]), DATESYTD('DateTable'[Date], "12/31"))
Prior Year YTD Sales =
CALCULATE(SUM('Sales'[Product Sales]), DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31"))Calculate Sales delta:
Sales Delta = [YTD Sales] - [Prior Year YTD Sales]
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Thanks for providing these calculations.
I created a test dashboard with a DateTable which I linked to the test data set (SalesDataTest). It appears that YTD Sales, Prior Year YTD Sales, and Sales Delta are calculating correctly. However, New Customer Sales Delta, Churn Sales Delta, and Existing Customer Sales Delta are blank in my Matrix.
Could you please have a look and see if I am missing anything?
v-sdhruvCommunity Support
Hi kc6859 ,
Thanks for sharing the file.
After analysing the measures used, I feel that you should use ALL in-place of Values.Sales Delta from Churned Customers = VAR _maxDate = MAX('DateTable'[Date]) VAR _currentYTD = DATESYTD('DateTable'[Date], "12/31") VAR _priorYTD = DATESYTD(DATEADD('DateTable'[Date], -1, YEAR), "12/31") RETURN SUMX( ADDCOLUMNS( ALL('SalesDataTest'[Customer]), "CurrentYTD", CALCULATE(SUM('SalesDataTest'[Product Sales]), 'DateTable'[Date] IN _currentYTD), "PriorYTD", CALCULATE(SUM('SalesDataTest'[Product Sales]), 'DateTable'[Date] IN _priorYTD) ), VAR CY = [CurrentYTD] VAR PY = [PriorYTD] RETURN IF(PY > 0 && CY = 0, -PY, 0) )and similarly for Existing and new customers.
This will populate the values for these measures because this will ensure you're evaluating every customer, not just the ones currently in filter context.
Below is the updated file attached for your reference.
Hope this helps!Thank you for providing the updated dashboard. Unfortunately this still doesnt match the desired output - sales detla from churn, existing customers, and new customers does calculate at the total level, but not at the customer level. This is what I'm seeing:
My solution is to do the analysis in Excel. I pull my data into an excel workbook by using the same PowerQueries as I am using in PBI. Then I'm able to add columns to the right of the data which automatically update. I set my input date into a table in Excel with 1 row to reference in calculations. I can then connect my PBI to the Excel workbook to import the analysis and the current period. This allows me to do significantly more flexible analysis while keeping the formulas simple.
Let me know if you have any suggestions to further refine this process
- v-sdhruv
Hi kc6859 ,
Your approach also works well with respect to current requirement.
However, you can also do Power BI only approach since Power BI can also be used for analysis.
You can make use of power-query or DAX measures to perform your initial analysis and then build visualizations on top of that.
Thank You
