Creating new table

[Anonymous]

Hi

I have the following challenge.  I have a table that contains customer orders in the form

<customer>, <order_value>, <time_stamp>

I want to create a new table that would contain

<customer>, <cumulative_orders_value>, <end_of_month>

the above table shall have

• for every month from the earliest order even placed, till the present date month , the last date of the month. This would be the <end_of_month> value
• the cumulative sales for the said customer of all orders ever placed up until and including the <end_of_month> period

thank you in advance

BR

Chronis

[PattemManohar]

@Anonymous Please post the sample data and expected output which will help to resolve the scenario quicker...

[Anonymous]

@PattemManohar lets assume we have the following data

the 3 columns on the left are sales from customes a and b

customer a places one order in Jan and one order in March

customer b places one order in Feb and one in april

in the output table we want for each month between Jan and March, for each customer, the the total number of sales up until the end of the month of the row.

so given that we have 2 customers, for every month there will be 2 rows (one for each customer)

I hope this clarifies the problem

[PattemManohar]

@Anonymous Thanks for providing the sample data and detailed explanation.

Here is the steps I've followed to achieve it

Step1 : Added new field in the source table as below

EOMonth = EOMONTH([Date],0)

Step2 : Created new table as below

CustVolOut = 
VAR _Temp = SELECTCOLUMNS(CustVol,"Customer",[Customer],"Volume",[Volume],"Dat",EOMONTH(CustVol[Date],0))
VAR _MinDate = MIN(CustVol[Date])
VAR _MaxDate = EOMONTH(MAX(CustVol[Date]),0)
VAR _Calender = FILTER(CALENDAR(_MinDate,_MaxDate),[Date]=EOMONTH([Date],0))
VAR _CrossJoin = CROSSJOIN(_Calender,_Temp)
VAR _Res = FILTER(ADDCOLUMNS(_CrossJoin,"Flag",LOOKUPVALUE(CustVol[Customer],CustVol[Customer],[Customer],CustVol[EOMonth],[Date])),[Flag]<>BLANK())
VAR _Res1 = ADDCOLUMNS(_Res,"Flag1",IF([Date]<>[Dat],"b","a"))
VAR _Res2 = ADDCOLUMNS(_Res1,"Volume1",IF([Flag]=[Flag1],[Volume],0))
VAR _Res3 = SELECTCOLUMNS(_Res2,"Customer",[Flag1],"Sales",[Volume1],"Period Ending",[Dat])
RETURN _Res3

Step3 : Add a new field in the table that was created in above Step2 

TotalCummSales = CALCULATE(SUM(CustVolOut[Sales]),FILTER(ALL(CustVolOut),CustVolOut[Customer] = EARLIER(CustVolOut[Customer]) && CustVolOut[Period Ending] <= EARLIER(CustVolOut[Period Ending])))

Finally, here is the output table..

I definitely believe, there might be more efficient way of doing the same, but this is the approach that is currently on top of my head. But I'll be glad if it atleast helps you a percent....

[Anonymous · Accepted solution]

@PattemManohar wow

this looks a lot more complicated that I expected !!!! thank you very much indeed. !!!

my only concearn is that the code seems to have the customer names "a" and "b" hardwired in the code. In the actual case there will be hundreds of customers.

[bethallcot]

Thank you for sharing this information, because I had the same problem at Eduzaurus.com. This tip was really helpful.