Offset + Match in PowerPivot

[illusionek]

Hello!!!

I got a fairly complicated Stock Cover calculation in Excel that I am trying to replicate in PowerPivot. Unfortunately this calculation in regular Excel uses a combination of Offset and Match to get right values. I was doing some reading online and apparently this can be replicated in PP using Calculate and Filter but so far I was unable to replicate my specific scenario.

I was wondering if anyone could help me out.

I attached a sample Excel file with offset + match functions to show how it is currently done. I have also attached an example of PowerPivot with the same data in normalized tables where I am trying to reflect this calculation.

Please help

http://www.filedropper.com/excel-samplefile

http://www.filedropper.com/powerpivot-examplefile

 

[illusionek]

Any ideas anyone? Please

 

[ImkeF]

The link that should deliver the Excel-data delivers the Power Pivot data - just as the 2nd link - please post useable data.

 

[illusionek]

Apologies, not sure whats happened. Please see both links below

http://www.filedropper.com/excel-samplefile_1

http://www.filedropper.com/powerpivot-examplefile_1

 

[ImkeF]

The example you've given is not dynamic, so it returns a value that only makes sense for the given week in B1 around the 23.05.2016. So the questions are:

1) How shall that reference-data-value be passed onto the model?
Or 2) Do you want the stock cover value to be calculated for any given value in your date-table?
3) Will this be calculated per article? Please post structure of the tables that make your data model

Please also post links to the articles you've mentioned. Thanks