How to create new data table with Measures as values

alya1 — Mon, 24 Jun 2024 16:45:18 GMT

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!

Re: How to create new data table with Measures as values

Ankur04 — Mon, 24 Jun 2024 18:23:31 GMT

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,

Re: How to create new data table with Measures as values

alya1 — Fri, 05 Jul 2024 22:24:30 GMT

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

topic Re: How to create new data table with Measures as values in DAX Commands and Tips

How to create new data table with Measures as values

Re: How to create new data table with Measures as values

Re: How to create new data table with Measures as values