Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All
I have a sample data with Date,Location,Product ID and Qty, I wanted to have cumulative value of Qty in a given location for a product by Date (data and Result is given below)
Data
ProductId | Date | Location | Qty |
59799839860 | 5/14/2018 | 4228 | 100 |
59799839860 | 5/24/2018 | 4228 | 120 |
59799839860 | 5/27/2018 | 4228 | 250 |
59799839860 | 6/7/2018 | 4228 | 60 |
59799839860 | 6/25/2018 | 4228 | 50 |
59799839860 | 7/2/2018 | 4228 | -500 |
59799839860 | 7/11/2018 | 4228 | 40 |
59799839860 | 8/20/2018 | 4228 | -40 |
59799839860 | 9/6/2018 | 4228 | 120 |
59799839860 | 10/7/2018 | 4228 | 200 |
59799839860 | 12/11/2018 | 4228 | 140 |
59799839860 | 12/31/2018 | 4228 | 230 |
59799839860 | 1/28/2019 | 4228 | 430 |
59799839860 | 5/22/2018 | 4344 | 250 |
59799839860 | 12/22/2018 | 4750 | 140 |
59799839860 | 12/22/2018 | 4817 | 120 |
59799839860 | 12/2/2018 | 4870 | 120 |
59799839860 | 12/5/2018 | 4870 | 125 |
Result
ProductId | Location | Date | Cumulative |
59799839860 | 4228 | 5/14/2018 | 100 |
59799839860 | 4228 | 5/24/2018 | 220 |
59799839860 | 4228 | 5/27/2018 | 470 |
59799839860 | 4228 | 6/7/2018 | 530 |
59799839860 | 4228 | 6/25/2018 | 580 |
59799839860 | 4228 | 7/2/2018 | 80 |
59799839860 | 4228 | 7/11/2018 | 120 |
59799839860 | 4228 | 8/20/2018 | 80 |
59799839860 | 4228 | 9/6/2018 | 200 |
59799839860 | 4228 | 10/7/2018 | 400 |
59799839860 | 4228 | 12/11/2018 | 540 |
59799839860 | 4228 | 12/31/2018 | 770 |
59799839860 | 4228 | 1/28/2019 | 1200 |
59799839860 | 4344 | 5/22/2018 | 250 |
59799839860 | 4750 | 12/22/2018 | 140 |
59799839860 | 4817 | 12/22/2018 | 120 |
59799839860 | 4870 | 12/2/2018 | 120 |
59799839860 | 4870 | 12/5/2018 | 245 |
Regards
Jayanthan
Solved! Go to Solution.
As a calc column,, try
Column = VAR mydate = [Date] RETURN CALCULATE ( SUM ( Table1[Qty] ), ALLEXCEPT ( Table1, Table1[ProductId], Table1[Location] ), Table1[Date] <= mydate )
As a calc column,, try
Column = VAR mydate = [Date] RETURN CALCULATE ( SUM ( Table1[Qty] ), ALLEXCEPT ( Table1, Table1[ProductId], Table1[Location] ), Table1[Date] <= mydate )
Hi Zubair
Thanks you for the support, it works
Regards
Jayanthan
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |