Beginning Inventory Formula: IF 'SYS01 Time Settings By Week'.'Not 1st Week of Timescale?' = TRUE THEN PREVIOUS (Ending Inventory) ELSE 'DAT01 Beginning Inventory'.Beginning Inventory[LOOKUP: 'SYS08 SKU Details'.Distribution Center]----Does this not look correct? Any help is greatly appreciated.
Let me try to help you here. What is the error you are getting?
In theory, this formula should get you the correct answer but it has 2 unnecessary elements;
You don't need to say "IF 'SYS01 Time Settings By Week'.'Not 1st Week of Timescale?' = TRUE". You need a condition after the IF that resolves to True or False. Since Not 1st Week of Timescale? is a boolean then it resolves to True or False and it's enough to say IF Not 1st Week of Timescale? THEN ... ELSE
You also don't necessarily need the LOOKUP in this case because each SKU has a beginning inventory in just one location. So if you don't specify the location, the formula will sum up all the Beginning Inventory which is 13,000 + 0 + 0 + 0 ..... for example, which is 13,000. I like your way of thinking and it would be a more sustainable way just in case we had beginning Inventory in more than one location.. This is the same way I teach people to think about lookup, Look at every dimension in the source and make sure it either matches (like P3 SKU) or you need to explicitly send a lookup value ( like distribution Center).
In any case, the above won't cause the formula to give errors or produce the wrong values necessary. So back to my first question... what is the error you are getting?
@aashca9 your formula seems correct , just one suggestion as per best practice while using IF else , use condition which will have an early exit i.e which will check the If condition less no of times, you see only 1 week is 1st week of Timescale rest all are as per the boolean, so if you use if not sys time.not 1st week then beginning inventory else previous ending inventory, it is more efficient as per best practice