Introduction
This week, our challenge is to calculate customer lifetime value by quarter using DAX. This is an advanced challenge so buckle up!
As I was working on my challenge for week 44, I stumbled upon the drafted challenge for Tableau on the same week. The Tableau challenge was… you guessed it… developing a chart showing customer lifetime value by quarter.
I began to think about the differences in how I would address this problem in Tableau and Power BI and decided it would be fun to tackle this for our challenge as well.
Big thanks to Luke Stanke for the initial idea and not minding that we steal it for this challenge!
NOTE: Datasets for Tableau and Power BI are slightly different so don’t aim to match the Tableau numbers.
Requirements
Challenge
Develop a stacked bar chart showing the total value of consumers for each quarter since their first order
- Total Bar Size = Running value of customers by quarter
- Bar 1 = Incremental Value by Quarter – Color: #5557EB
- Bar 2 = Previous Quarters Value (Total Value – Quarterly Incremental Value) – Color: #BCBCF7
Calculations Needed (Must be done in DAX)
- First Order Date – For each row based on Customer ID
- Max Quarters as Customer – Calculate the max number of months a customer could have existed based on difference of the first order date (of the customer) and the last order date (for all orders)
- Quarters Since First Purchase – For each row based on Customer ID and Order Date
- Customer Count by Quarter Since First Order – Calculate the total customers that are eligible to have sales for each quarter
- Avg Quarterly Value by Customer
- Avg Quarterly Value by Customer (SUMX)
- Wrapped in SUMX over VALUES of calculation 3 to fix aggregation issues
- HINT: This is Bar 1
- Running Total of Avg Quarterly Value – Using calc 6
- Running of Quarterly Value for Previous Quarter
- Calc 7 – Calc 5
- HINT: This is Bar 2
Hints
- Google is your friend
- If you have only been a customer for 4 months, then you cannot be included when calculating average value after quarter 4
- Separate the logic when calculating sales and customer cohorts
- Find a way to make the quarterly cohort value and the quarters since first order work together while being calculated separately
- Calculate your quarter cohort to be the distance between the customers first order and the max order for all customers is one method
Dataset
This week’s dataset is available here.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, @NerdyWithData and @ThysvdW. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
For the calendar I use this DAX function:
Calendar =
CALENDAR ( FIRSTDATE ( ‘Calendar'[Date] ), LASTDATE ( ‘Calendar'[Date] ) )
Is there any difference in my approach than yours?
I based my date information on the order dates within the data itself; however, there are a few different ways to do this. As long as you are using that table from your calculation to get to the same outcome, there should be absolutely no issues with your approach.