cancel
Showing results for
Did you mean:
Helper III

## Cumulative Total by Product/Location/Date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

As a calc column,, try

```Column =
VAR mydate = [Date]
RETURN
CALCULATE (
SUM ( Table1[Qty] ),
ALLEXCEPT ( Table1, Table1[ProductId], Table1[Location] ),
Table1[Date] <= mydate
)
```
Regards,
Zubair

2 REPLIES 2
Super User I

As a calc column,, try

```Column =
VAR mydate = [Date]
RETURN
CALCULATE (
SUM ( Table1[Qty] ),
ALLEXCEPT ( Table1, Table1[ProductId], Table1[Location] ),
Table1[Date] <= mydate
)
```
Regards,
Zubair

Helper III

Hi Zubair

Thanks you for the support, it  works

Regards

Jayanthan

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors