Hi
My Finance Director wants to show when monies are due per Purchase Order Received (simple calc of week num PO is issued + known or estimated lead time to when billing can occur + payment terms....)
However when we record PO's by week numbers some weeks are missing - i.e. we didn't receive PO's that week.
How can I show against a list of PO's which weeks in the year monies are due, including weeks where no PO's received?
I know I could manually check for empty weeks and manually add a 'dummy Po' with Zero value but this is a pain with hundreds of items to sort through each time the report is run, and Ideally the report should be able to be run with out me being present.
Therefore - is there a way to add these blank weeks into the report as part of a power query or power pivot? Happy with either if possible - but only posted here as the results will be created using Power Pivot, so it would be nice to keep it all together.
Any help gratefully received.
To Recap
I have data such as
Week No PO Value
1 1234 £233
2 1235 £75
2 1256 £690
4 1259 £450
What I need is to include week 3 with Zero Value and Nill PO's
Result Week No/ 1 2 3 4
PO 1234 £233
1235 £75
1256 £690
1259 £450
Total for Week £233 £765 £0 £450
PS - only just started using this wonderful tool after a break of 3 years where previous employer wouldn't spring for the right version of Excel/Office.
Hi Dave,
Great to see you're back on the Excel wagon and making use of Power Pivot.
You need to have a 'Date' or 'Calendar' table in your model. This table has every date listed (no gaps in dates). You can add columns to this table for the Week Number, Month, Year etc. that you would want to summarise your data by. Link your fact table to the Date table via the Date field common in both.
Then write a measure
Note: Change table and column names to suit your database.
Use the measure in your PivotTable values area and the Date table date fields in your row/column labels.
Mynda
Hi Mynda
Thanks for the reply - this works!
I was heading in the correct direction, but was only using week numbers, and I think also pulling from the WeekNr from the Dimension table and this only works if you pull dates from the facts table into the pivot.
Thanks again
