Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
2 years ago
Solved

How to create new data table with Measures as values

Hi All, 

 

I have raw data as below:

table 1 customer list throughout the years uploaded daily

customer id   date   
1114/3/2021
2224/3/2021
3334/3/2021
1114/21/2021
2224/21/2021
3334/21/2021
1114/22/2021
2224/22/2021
3334/22/2021
4444/22/2021
2225/2/2021
3335/2/2021
4445/2/2021
5555/2/2021
11110/1/2023
33310/1/2023
44410/1/2023
55510/1/2023

 table 2 customers who left and dates

customer id   action   date   
111Left4/28/2021
222Left9/9/2023

table 3 customer who joined and dates

customer id   action   date   
111Joined1/1/2021
222Joined4/1/2021
333Joined4/1/2021
444Joined4/22/2021
555Joined5/1/2021
111Joined9/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  
2021Jan32100
2021Feb56102


Thank you! 

  • 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,

     

2 Replies

  • 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,