sandwichgirl
New Member
- Joined
- Aug 13, 2007
- Messages
- 31
- Office Version
- 365
- Platform
- 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.
Here is how I would like it to eventually look with CF.
TIA
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 | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 25 | Info Table | Forecast Table | Stock Table | |||||||||||||||||||
| 26 | Code | Prod Code | Description | Code | Prod Code | Description | Attribute | Value | Code | Stock at site | Stock at supplier | WIP | Avail1 | Prod | Avail2 | Next | Avail3 | |||||
| 27 | RC18125 | 1 | Item 1 | RC18125 | 1 | Item 1 | Tue 23/11/2021 | 15961 | RC18125 | 23920 | 61950 | 25000 | 08/01/2022 | 25000 | 08/02/2022 | 55000 | 08/03/2022 | |||||
| 28 | RC18126 | 2 | Item 2 | RC18125 | 1 | Item 1 | Tue 30/11/2021 | 15961 | RC18126 | 15890 | 0 | 0 | 0 | 42000 | 12/12/2021 | |||||||
| 29 | RC18127 | 3 | Item 3 | RC18125 | 1 | Item 1 | Tue 07/12/2021 | 15961 | RC18127 | 2490 | 31875 | 0 | 89000 | 15/12/2021 | ||||||||
| 30 | RC18128 | 4 | Item 4 | RC18125 | 1 | Item 1 | Tue 14/12/2021 | 15961 | RC18128 | 14085 | 88875 | 0 | 85000 | 15/12/2021 | ||||||||
| 31 | RC18129 | 5 | Item 5 | RC18125 | 1 | Item 1 | Tue 21/12/2021 | 11072 | RC18129 | 9900 | 0 | 0 | 60000 | 07/12/2021 | ||||||||
| 32 | RC18130 | 6 | Item 6 | RC18125 | 1 | Item 1 | Tue 28/12/2021 | 14700 | RC18130 | 17255 | 0 | 0 | 55000 | 07/12/2021 | ||||||||
| 33 | RC18131 | 7 | Item 7 | RC18125 | 1 | Item 1 | Tue 04/01/2022 | 14700 | RC18131 | 7700 | 0 | 15000 | 15/11/2021 | 0 | ||||||||
| 34 | RC18132 | 8 | Item 8 | RC18125 | 1 | Item 1 | Tue 11/01/2022 | 14700 | RC18132 | 5880 | 0 | 7000 | 15/11/2021 | 21000 | 15/12/2021 | |||||||
| 35 | RC18125 | 1 | Item 1 | Tue 18/01/2022 | 14700 | |||||||||||||||||
| 36 | RC18125 | 1 | Item 1 | Tue 25/01/2022 | 14700 | |||||||||||||||||
| 37 | RC18125 | 1 | Item 1 | Tue 01/02/2022 | 14700 | |||||||||||||||||
| 38 | RC18125 | 1 | Item 1 | Tue 08/02/2022 | 14700 | |||||||||||||||||
| 39 | RC18125 | 1 | Item 1 | Tue 15/02/2022 | 14700 | |||||||||||||||||
| 40 | RC18125 | 1 | Item 1 | Tue 22/02/2022 | 14700 | |||||||||||||||||
| 41 | RC18125 | 1 | Item 1 | Tue 01/03/2022 | 14700 | |||||||||||||||||
| 42 | RC18125 | 1 | Item 1 | Tue 08/03/2022 | 14700 | |||||||||||||||||
| 43 | RC18125 | 1 | Item 1 | Tue 15/03/2022 | 14700 | |||||||||||||||||
| 44 | RC18125 | 1 | Item 1 | Tue 22/03/2022 | 14700 | |||||||||||||||||
| 45 | RC18125 | 1 | Item 1 | Tue 29/03/2022 | 14700 | |||||||||||||||||
| 46 | RC18125 | 1 | Item 1 | Tue 05/04/2022 | 14700 | |||||||||||||||||
Sample | ||||||||||||||||||||||
Here is how I would like it to eventually look with CF.
TIA
