Forum Discussion
Customer Analytics with Sales Data set and Dynamic YTD date range
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
Super UserHi 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?