Forum Discussion
Creating new table
- Anonymous8 years ago
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.
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....
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.