Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
thank you in advance
BR
Chronis
Solved! Go to 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.
@Anonymous Please post the sample data and expected output which will help to resolve the scenario quicker...
Proud to be a PBI Community Champion
@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
@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....
Proud to be a PBI Community Champion
@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.
Thank you for sharing this information, because I had the same problem at Eduzaurus.com. This tip was really helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |