I opened your book - I have used that particular solution before and found it to be difficult to get right - it relies on a number of nested table calculations that need to created and used in a specific sequence
But at the convention this year at tc18 Archana Ganeshalingam and Sasha Singh did a session on creating date scaffolds that I think is a more straight forward approach - it is recorded and has several step by step examples -
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
This video goes really fast and assume I have a date_dim$ file setup in advance. I cannot find anything that tells me how a date_dim$ file needs to be setup. Can you advise?
All you are doing is creating a list of all the dates that you need in your analysis (ALL the dates including the ones where you don't have any records) - so it is created at the level you want to see and will also match the date level of your data your data is all in years so you list (scaffold) will be all the years (I would use a real date like 1/1/2019) from the first year in your data to the last year your want to include in the analysis - eg if you want to extend it out to 2025 for expected deliveries then include each year 2020 -2025)
make it a single column in excel and with any heading and join it to your existing data on the actual date column in your data (is year the actual data or is there another?)
the video is easy enough to pause - so take it a step at a time -
I figured out how to create a date_dim$ source file on my own. For those reading after me, here are the instructions.
1) Create a new Excel file
2) Label the 1st column "Calendar"
3) Create a list of dates in Day format "1/1/2019" regardless of whether you care about days, weeks, months or years. Increment the dates in the step you require. I only required years, so I used "1/1/2018", "1/1/2019", etc.
4) Save the files as date_dimension
I hope this helps users who read this after me.