Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have raw data as below:
table 1 customer list throughout the years uploaded daily
customer id | date |
111 | 4/3/2021 |
222 | 4/3/2021 |
333 | 4/3/2021 |
111 | 4/21/2021 |
222 | 4/21/2021 |
333 | 4/21/2021 |
111 | 4/22/2021 |
222 | 4/22/2021 |
333 | 4/22/2021 |
444 | 4/22/2021 |
222 | 5/2/2021 |
333 | 5/2/2021 |
444 | 5/2/2021 |
555 | 5/2/2021 |
111 | 10/1/2023 |
333 | 10/1/2023 |
444 | 10/1/2023 |
555 | 10/1/2023 |
table 2 customers who left and dates
customer id | action | date |
111 | Left | 4/28/2021 |
222 | Left | 9/9/2023 |
table 3 customer who joined and dates
customer id | action | date |
111 | Joined | 1/1/2021 |
222 | Joined | 4/1/2021 |
333 | Joined | 4/1/2021 |
444 | Joined | 4/22/2021 |
555 | Joined | 5/1/2021 |
111 | Joined | 9/30/2023 |
Please note that all data is for past 3 years with thousands of customer IDs and with relationship to a date table.
Then, I created a measures table with measures for:
Customer Leave (COUNTA(table 2[date])
Customer Join (COUNTA(table 3[date])
Average Customers (AVERAGEX(VALUES(table 1[date]), CALCULATE(COUNTA(table 1[customer ID])))
Currently, I'm displaying the measures as line/bar charts with dates as x-axis.
How can I create a new data table (not just visual) with columns Month, Year, calculated Join Count, calculated Leave Count, and calculated Average Customers? This is because I want to do forecasting on the columns down the road.
Something like this (random numbers)
Year | Month | Join Count | Leave Count | Average Customers |
2021 | Jan | 3 | 2 | 100 |
2021 | Feb | 5 | 6 | 102 |
Thank you!
Solved! Go to Solution.
Hi,
try writing something like below,
New table = Summarizecolumns( your date table [Year],your date table [Month],"Join Count", [Customer Join],"Leave Count",[Customer Leave],"Average Customers",[Average Customers])
Note: [Average Customers] = your measure name.
please let me know it this works.
Thanks,
Hi,
try writing something like below,
New table = Summarizecolumns( your date table [Year],your date table [Month],"Join Count", [Customer Join],"Leave Count",[Customer Leave],"Average Customers",[Average Customers])
Note: [Average Customers] = your measure name.
please let me know it this works.
Thanks,
thank you it works! I ended up creating a another measure like this video below to forecast future average customers but the numbers don't match in the new table. Do you have any idea why?
https://www.youtube.com/watch?v=9Mj592bSKq0
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |