Transform excel offset formula into PQ format - calculate run out dates for 4 measures

sandwichgirl

New Member
Joined
Aug 13, 2007
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hello

I am relatively new to PQ and so far have used PQ to generate the tables/queries that my spreadsheet uses, but now would like to see if it is possible to get PQ to generate the full spreadie.

My spreadie takes our 4 stock room figures and calculates the run out dates vs the usage forcast, I then use CF to colour code it for our sales team to use as a quick stock reference.

I've attached a mini sheet that contains the tables I've set up so that I can PQ it and also an image of what the finished forecast will look like with conditional formatting which is based on the dates generated. I've changed some of the values in the stock table to keep the mini sheet small, so the CF wouldn't be exactly like the image but hopefully you get the gist.

My specific question is "How can I replicate the run out dates using PQ". They are currently an offset formula that I have no idea how to replicate in DAX. In my sheet in column AE the formula is:
=INDEX($F$6:$Y$6,MATCH(TRUE,SUBTOTAL(9,OFFSET(F7:Y7,,,,COLUMN($F$6:$Y$6)-COLUMN(F7)+1))>+AD7,0)) AD7 contains the "Stock at Site" figure in the mini sheet.
If you could help me with this first formula, I should hopefully be able to use it to generate the other dates too.

It'd be amazing if I can replicate the spreadsheet into PQ just for the satisfaction of achieving it, but if you tell me it isn't possible, then that's OK too.

Book5.xlsx
BCDEFGHIJKLMNOPQRSTU
25Info TableForecast TableStock Table
26CodeProd CodeDescriptionCodeProd CodeDescriptionAttributeValueCodeStock at siteStock at supplierWIPAvail1ProdAvail2NextAvail3
27RC181251Item 1RC181251Item 1Tue 23/11/202115961RC1812523920619502500008/01/20222500008/02/20225500008/03/2022
28RC181262Item 2RC181251Item 1Tue 30/11/202115961RC18126158900004200012/12/2021
29RC181273Item 3RC181251Item 1Tue 07/12/202115961RC1812724903187508900015/12/2021
30RC181284Item 4RC181251Item 1Tue 14/12/202115961RC18128140858887508500015/12/2021
31RC181295Item 5RC181251Item 1Tue 21/12/202111072RC181299900006000007/12/2021
32RC181306Item 6RC181251Item 1Tue 28/12/202114700RC1813017255005500007/12/2021
33RC181317Item 7RC181251Item 1Tue 04/01/202214700RC18131770001500015/11/20210
34RC181328Item 8RC181251Item 1Tue 11/01/202214700RC1813258800700015/11/20212100015/12/2021
35RC181251Item 1Tue 18/01/202214700
36RC181251Item 1Tue 25/01/202214700
37RC181251Item 1Tue 01/02/202214700
38RC181251Item 1Tue 08/02/202214700
39RC181251Item 1Tue 15/02/202214700
40RC181251Item 1Tue 22/02/202214700
41RC181251Item 1Tue 01/03/202214700
42RC181251Item 1Tue 08/03/202214700
43RC181251Item 1Tue 15/03/202214700
44RC181251Item 1Tue 22/03/202214700
45RC181251Item 1Tue 29/03/202214700
46RC181251Item 1Tue 05/04/202214700
Sample


Here is how I would like it to eventually look with CF.
1638391788629.png


TIA
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top