Hello! I have a running inventory report in place, but have been requested to include an "age classification" that changes annually, for now assume May 1st. In the example, start out on Dec 31,2016 with 91 animals, with 9 animals are sold up to April 30th, resulting with 82 animals on inventory April 30th.
On May 1st, the age classification changes from 1yr old's to 2yr old;s, and on the May 31st inventory, I would like the report to flip the prior inventory into the new age classification, less the two animals sold in May.
The measure I currently have in place is listed below (please note, I am using parameters with the || operator, this appears to be working correctly...
End Invty Qty =
CALCULATE (
[Sum of Quantity],
'Consolidated Transactions'[Account] = CropsInStorageMrktAsset
|| 'Consolidated Transactions'[Account] = LivestockMrktAsset,
FILTER (
ALL ( 'CALENDAR'[Date] ),
'CALENDAR'[Date] <= MAX ( 'CALENDAR'[Date] )
)
)
Below table is a summary of dates, inventory change, along with the current (wrong) result, and desired result. As you can see in May, that is where things go wrong. And that is related to age classification change on May1st.
| | | | | | |
| | | Current Result | Current Result | Desired Result | Desired Result |
| Month | InvtyChge | 1+YrOld | 2+YrOld | 1+YrOld | 2+YrOld |
| Dec-16 | | 91 | | 91 | |
| Jan-17 | -2 | 89 | | 89 | |
| Feb-17 | -7 | 82 | | 82 | |
| Mar-17 | 0 | 82 | | 82 | |
| Apr-17 | 0 | 82 | | 82 | |
| May-17 | -2 | 82 | -2 | | 80 |
| Jun-17 | 3 | 82 | 1 | | 83 |
This final table is a sampling of the fact table 'Consolidated Transactions' as referenced in the above measure. Quantity represents inventory change.
| Account | Vendor | Amount | Quantity | Date | ProdStage |
| 1315 | Intial StartupBalances | 136500 | 91 | 12/31/2016 | 1+YrOld |
| 1315 | Source Query: MrktPriceChange | -3000 | -2 | 1/31/2017 | 1+YrOld |
| 1315 | Source Query: MrktPriceChange | -10500 | -7 | 2/28/2017 | 1+YrOld |
| 1315 | Source Query: MrktPriceChange | -3000 | -2 | 5/31/2017 | 2+YrOld |
| 1315 | Source Query: MrktPriceChange | 4500 | 3 | 6/30/2017 | 2+YrOld |
I am hoping this can be resolved with a measure? Most likely I will have differing "months" for age classification change for various animal groups or crop inventory types - but we can deal with that later? Thank you in advance for your assistance!
