Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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...
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 |
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!
Ok, I have attached a link to a very simplified data set. The group 80114A should match the initial explanation. But, once you see the visual - the displayed inventory, starting in May needs to be shifted over one classification +/- any inventory change.
Hope this brings further clarity!
thanks
https://drive.google.com/file/d/1hYG3iMrZxobIkcfmf2SoV49YvqGuPtGo/view?usp=sharing
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |