cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
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

 

ProductIdDateLocationQty
597998398605/14/20184228100
597998398605/24/20184228120
597998398605/27/20184228250
597998398606/7/2018422860
597998398606/25/2018422850
597998398607/2/20184228-500
597998398607/11/2018422840
597998398608/20/20184228-40
597998398609/6/20184228120
5979983986010/7/20184228200
5979983986012/11/20184228140
5979983986012/31/20184228230
597998398601/28/20194228430
597998398605/22/20184344250
5979983986012/22/20184750140
5979983986012/22/20184817120
5979983986012/2/20184870120
5979983986012/5/20184870125

 

 

Result

ProductIdLocationDateCumulative
5979983986042285/14/2018100
5979983986042285/24/2018220
5979983986042285/27/2018470
5979983986042286/7/2018530
5979983986042286/25/2018580
5979983986042287/2/201880
5979983986042287/11/2018120
5979983986042288/20/201880
5979983986042289/6/2018200
59799839860422810/7/2018400
59799839860422812/11/2018540
59799839860422812/31/2018770
5979983986042281/28/20191200
5979983986043445/22/2018250
59799839860475012/22/2018140
59799839860481712/22/2018120
59799839860487012/2/2018120
59799839860487012/5/2018245

 

Regards

Jayanthan

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

@jayanthan 

 

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


View solution in original post

2 REPLIES 2
Super User I
Super User I

@jayanthan 

 

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


View solution in original post

 Hi Zubair 

 

Thanks you for the support, it  works

 

 

Regards

Jayanthan

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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