Dynamic cumulative sum fill rate

[Anonymous]

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

[V-lianl-msft]

Hi @Anonymous ,

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.

[Anonymous]

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

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

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.

[Anonymous]

thanks a lot! It works perfectly 😎