I have a formula that automatically calcutes the change in inventory from one week to another. This formula worked for several weeks until recently. Our IT pushed an update to our Date Calendar (DimDate) that I believe affected the results to my formula below. I'm stumped and can't figure out what happened. My hope is that a second set of eyes can look at this and possibly figure out what is wrong.
Note: I believe it was switched from import mode to direct query. I know for a fact it is currently in direct query mode.
The result should show ~30,000. Instead it is showing ~300,000. Our Dimdate calendar goes back to 2009, could it be that it is summing 10 years worth of inventory instead of just the previous weeks entered inventory?
-SUM() formula just sums the inventory quantity entered
- FiscalWeekofYear is a formatted decimal number 1-52. The first week of January would be 1.
- FiscalYear is formatted as a decimal number also. It says 2019 or 2020 or 2018.
- FiscalDayofWeek is a number 1-7. I want to grab the inventory entered on day "6"
- The inventory type is just grabbing a "daily" entered inventory vs a "monthly" entered inventory in case the days align. Not that important.
- The MainProductName is the product type of the inventory I want to sum
My first assumption was that this formula stopped working because of the switch from import mode to direct query. I just had IT switch only the DimDate table back over to Import Mode, and the problem still remains.
So, now i have the DimDate table in import mode and the FactInventory and all other tables in direct query.
Yes, when the data was switched over IT re-mapped the relationships. There very well could be a problem there. Building out test data might be difficult but would you mind if i just messaged you the actual file? There's nothing really discrete about it but I would prefer not posting it to the internet.