Cumulative total by category and date

[Anonymous]

Hello,

I need some help in calculating running total. 

Available quantity , quantity needed are measures I created.
Parent product and product are coming from two separate tables. Date is coming from the third table.  There is a slicer to select the dates. 

I need help in creating a measure for calculating running total by product within the selected dates. 

Thank you so much in advance. 

The calculated column that I am creating is something like this. I am getting the same values as quantity needed column.

RT = CALCULATE(
SUMX( REQUIREMENT,REQUIREMENT[quantity needed]),
FILTER ( ALLSELECTED( WORK_ORDER[DESIRED_WANT_DATE]),
WORK_ORDER[DESIRED_WANT_DATE]<=MAX(WORK_ORDER[DESIRED_WANT_DATE]) &&
WORK_ORDER[DESIRED_WANT_DATE] >= MIN(WORK_ORDER[DESIRED_WANT_DATE])),
GROUPBY(REQUIREMENT,REQUIREMENT[PART_ID])
)

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data (exclude sensitive data) in table REQUIREMENT and WORK_ORDER in order to make troubleshooting? Is there any relationship created among Date,  REQUIREMENT and WORK_ORDER table? If yes, please also provide the related relationship fields. Besides this, please provide the formula of measure [Available Quantity] and [Quantity Needed].  It is better if you can provide a sample pbix file.Thank you.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

@v-yiruan-msft @amitchandak 

I have tried creating a new column in the requirement table balance and test. Now what is happening is if the work order want date is same. It is all being grouped together. 

test = CALCULATE(sumx(REQUIREMENT,REQUIREMENT[Balance]),filter( REQUIREMENT, REQUIREMENT[PART_ID] = EARLIER(REQUIREMENT[PART_ID]) && REQUIREMENT[WORK_ORDER.DESIRED_WANT_DATE] <=EARLIER(REQUIREMENT[WORK_ORDER.DESIRED_WANT_DATE])))

[Anonymous]

@v-yiruan-msft  The schema looks something like this.  Part 2 is a copy of the part table because I needed parts needed to make the bigger part. 

Work Order and requirement table have a relationship with work order id. 

Date is the work order date. 

Available qty is coming directly from part2 table. created a simple measure for it using sumx.

balance is total qty needed - requirement[issued qty]. I am looking for cumulative sum of balance by part2[id]. I need to subtract that sum from available qty to see which work orders can be completed in selected dates.

For example:  I need this part_id to complete these 8 work orders and I only have 48 in stock right now.  If I can get a cumulative sum of balance by partid , I can subtract that value from qty on hand to see which work orders can be completed.

[amitchandak]

@Anonymous , Option as new measures and new column (Running Total

new column = sumx(filter( Table, [product] =earlier([product]) && [Date] <=earlier([Date])), [Quantity needed])

new measure = sumx(filter( allselected(Table), [product] =max([product]) && [Date] <=max([Date])), [Quantity needed])

[Anonymous]

@amitchandak , date is coming from table 1, parent part is coming from table 1 and part is coming from table3. available quantity is coming from table 3 and quantity 3 is a measure based on columns from table 1

The calculated column that I am creating is something like this.  I am getting the same values as quantity neededcolumn.

RT = CALCULATE(

SUMX( REQUIREMENT,REQUIREMENT[quantity needed]),

FILTER ( ALLSELECTED( WORK_ORDER[DESIRED_WANT_DATE]),

WORK_ORDER[DESIRED_WANT_DATE]<=MAX(WORK_ORDER[DESIRED_WANT_DATE]) &&

WORK_ORDER[DESIRED_WANT_DATE] >= MIN(WORK_ORDER[DESIRED_WANT_DATE])),

GROUPBY(REQUIREMENT,REQUIREMENT[PART_ID])

)

what is happening is it is taking cumulative sum by date. for example : multiple work order on one date. It is showing me the sum of all the work orders due for that that. and then sum of all the work order for another date.