Forum Discussion
Cumulative sum
HI I am calculation the cumulative sum of the product sold with the sold date but getting some unwanted result for some dates with multiple sells.
Sold Date Item No Of Product sold Running Total Desired Result
8/10/21 A 2 2 2
8/12/21 B 1 3 3
8/14/21 A 3 6 6
8/15/21 B 1 8 7
8/15/21 B 1 8 8
For the 8/15/21 it is adding the Product Sold (1+1) and adding in the Running Total.
Dax I am using is CALCULATE ( [TOTAL QTY], FILTER(ALL(Sales Table),Sales_date <= MAX(Sales_date))
Note my sales_date is not continueous.
Thanks
BishwaR based on your data, it is not going to work, you need to break the ties between the dates. You can add index column in PQ and then instead of date use that column for cumulative sum.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
4 Replies
- parry2kSuper User
BishwaR based on your data, it is not going to work, you need to break the ties between the dates. You can add index column in PQ and then instead of date use that column for cumulative sum.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- BishwaRHelper V
Could you please let me know how to generate a series in power query ?
is it list.generate something like that in the custom column ? I want a dynamic list
- parry2kSuper User
BishwaR you can add an index column:
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- BishwaRHelper V
Thanks a lot