Dynamic multilevel inventory coverage

[emark_lamper]

Hi experts, I am trying to come up with calculation of inventory coverage for each month (how many months will the forecasted inventory cover the forecasted requirements) which would be possible to drill down ItemClass > ItemGroup > Item. The measure has to be dynamic as the forecasts for a new month are added every month.

I’ve got data like this:

[

ItemClass,ItemGroup,Item,Date,Inventory,Requirements

x,a,1,20190831,179,100

x,a,1,20190930,163,80

x,a,1,20191031,147,98

x,a,1,20191130,131,120

x,a,1,20191231,115,70

x,a,1,20200131,99,40

x,a,2,20190831,176,36

x,a,2,20190930,159,42

x,a,2,20191031,142,25

x,a,2,20191130,125,36

x,a,2,20191231,108,30

x,a,2,20200131,91,87

x,a,3,20190831,732,249

x,a,3,20190930,483,189

x,a,3,20191031,694,203

x,a,3,20191130,491,216

x,a,3,20191231,575,173

x,a,3,20200131,702,218

x,b,5,20190831,4730,1607

x,b,5,20190930,3123,1639

x,b,5,20191031,3356,1559

x,b,5,20191130,2421,1419

x,b,5,20191231,1626,768

x,b,5,20200131,1482,534

x,b,6,20190831,8704,1513

x,b,6,20190930,7191,2159

x,b,6,20191031,13144,6014

x,b,6,20191130,10874,6603

x,b,6,20191231,4895,4046

x,b,6,20200131,2097,690

];

Example: For ItemClass=x ItemGroup=a Item=1 Date=20190831

Inventory (179) > Requirements in 20190831(100) and Inventory (179) <= Requirements in 20190831(100) + Requirements in 20190930(80), therefore Inventory Cover = 1month+(179-100)/80=1.9875months.

For ItemGroup aggregation: ItemClass = x ItemGroup=a Date=20190831

Inventory = 179+176+732=1087

Requirements in 20190831 = 385

Requirements in 20190930 = 311, etc.

Inventory Cover = 3+(1087-(385+311+326))/372=3.174731

I used rolling sums in script to generate fields which I used to define a master measure "".

Unfortunately, the expression involves 6 if statements which has impact on performance, and it would have to be changed whenever new monthly forecasts are added.

Please see attached app. 

Any ideas?

Best regards,

Martin