Guitarmageddon
Board Regular
- Joined
- Dec 22, 2014
- Messages
- 161
Hey folks, scratching my head on this one. I have a data model thats essentially three things.
1. A MONTHLY snapshot table of how many units are shipped out of two different warehouses. It goes down to the SKU level, but there are columns for month, department and other item level data etc.
2. A MONTHLY snapshot table of inventory at these two different warehouses, taken at the end of each month. Again, down to SKU level, contains item level data, and a column with the MONTH the report was taken.
3. A master date table (also have coresponding columns with our FISCAL periods and week numbers, since we do retail 4-5-4 but that shouldnt be a huge deal.
All that said, Im having a heck of a time doing anything with DAX measures to summarize either of these two tables together.
For example, I need to take the SUM of total units on hand and divide that by the units shipped within a given month to approximate a days of supply.
I.E. : (SUM of units shipped in JULY / SUM of inventory snapshot end of JULY) * 30 to get days of supply.
However, none of the DAX measures are cooperating and they just revert to a sum total for all data and weird stuff like that. The business side is trying to get to a monthly Days of supply measurement using the calc I stated above, or something close. Anyone have any nuggets of wisdom when dealing with these snapshot fact tables?
1. A MONTHLY snapshot table of how many units are shipped out of two different warehouses. It goes down to the SKU level, but there are columns for month, department and other item level data etc.
2. A MONTHLY snapshot table of inventory at these two different warehouses, taken at the end of each month. Again, down to SKU level, contains item level data, and a column with the MONTH the report was taken.
3. A master date table (also have coresponding columns with our FISCAL periods and week numbers, since we do retail 4-5-4 but that shouldnt be a huge deal.
All that said, Im having a heck of a time doing anything with DAX measures to summarize either of these two tables together.
For example, I need to take the SUM of total units on hand and divide that by the units shipped within a given month to approximate a days of supply.
I.E. : (SUM of units shipped in JULY / SUM of inventory snapshot end of JULY) * 30 to get days of supply.
However, none of the DAX measures are cooperating and they just revert to a sum total for all data and weird stuff like that. The business side is trying to get to a monthly Days of supply measurement using the calc I stated above, or something close. Anyone have any nuggets of wisdom when dealing with these snapshot fact tables?
