I just spent way too long researching this topic, coming up empty, and almost figuring it out a couple times before the answer slapped me in the face. Maybe everyone is already doing this, but hopefully it helps someone out.
I have a DPM cube and my Detailed Planning Object has multiple transactions on different dates - it’s big ticket inventory I track by serial that starts production, ends production, gets shipped, etc. I also am on a 4-4-5 calendar. Using the Date Diff method that is in the training materials yields results based on the calendar month. When a fiscal period ends on the last day or crosses calendar months you get missing or wrong time periods. And if you are using Date Diff for anything but testing true/false it’s arbitrarily not the real number of days. This has real consequences, like miscalculating depreciation by time period.
My solution is to convert everything into a date serial. Calc 1 is Date Diff, starting at 1/1/1900. You have to add Calc2, a formula to add 2 to Calc 1 because of counting…1/1/1900 is the min date the db accepts otherwise you could start at 12/30/1899. Then I added two global vectors for the serial of the period start and end dates, and then Calc3 is banding to test of Calc2 is between the two global vectors. Make sure your end date is the next weeks start date rather than the actual end date because the banding function uses < for the upper but >= for the lower limit. Now you can also check the actual days from the end of the period if that is what you wanted to do. Or 100 other things.
This was the fastest way I could find to put data into a fiscal period that worked. I’m surprised I couldn’t even find the question asked, which makes me think maybe it’s already been answered? Or was obvious?
