cancel
Showing results for
Did you mean:
Frequent Visitor

## Dynamic cumulative sum fill rate

I'd like to create calculated columns to get cumulative sum of sets ordered and sets shipped to have fill rate cumulative data, and if I select more than 2 weeks the calculation considers the data for those dates as the whole range

can somebody help?

 Date Order Fill Rate Status Sets Ordered Sets shipped fill rate cumulated sets ordered cumulated sets shipped fill rate **bleep** 19-Apr-21 O1001517088 96.43% Shipped>=95% 10945 10554 0.964276 10945 10554 0.964275925 19-Apr-21 O1001517075 95.10% Shipped>=95% 9477 9013 0.951039 20422 19567 0.958133386 19-Apr-21 O1001515196 95.87% Shipped>=95% 8400 8053 0.95869 28822 27620 0.958295746 26-Apr-21 O1001526467 88.34% Shipped<95% 9612 8491 0.883375 38434 36111 0.939558724 26-Apr-21 O1001524641 89.43% Shipped<95% 10220 9140 0.894325 48654 45251 0.930057138 26-Apr-21 O1001524629 96.52% Shipped>=95% 9630 9295 0.965213 58284 54546 0.935865761
1 ACCEPTED SOLUTION
Community Support

Try:

``````Measure =
var ordered_ = CALCULATE(SUM('Table'[Sets Ordered]),ALLSELECTED('Table'))
var shipped_ = CALCULATE(SUM('Table'[Sets shipped]),ALLSELECTED('Table'))
return DIVIDE(shipped_,ordered_,0)``````

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Community Support

After adding an index column in the query editor, create the following column:

``Column = CALCULATE(SUM('Table'[Sets Ordered]),FILTER('Table',EARLIER('Table'[Date])>='Table'[Date]&&'Table'[Index]<=EARLIER('Table'[Index])))``

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

thanks for the feedback!

what I am missing now is to have a final fill rate by week, so if I add two or more weeks the fill rate changes accordingly

Community Support

Try:

``````Measure =
var ordered_ = CALCULATE(SUM('Table'[Sets Ordered]),ALLSELECTED('Table'))
var shipped_ = CALCULATE(SUM('Table'[Sets shipped]),ALLSELECTED('Table'))
return DIVIDE(shipped_,ordered_,0)``````

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

thanks a lot! It works perfectly 😎

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.