Good morning all, I'm hoping someone can help me out here. I'm very new to this and banging my head against a wall.
I am trying to pull information from a warehouse management database and use it to create reports in dax. I have a situation which seemed simple, but has grown more and more complicated.
Basically I have a "Tasks" database that monitors tasks.
When a pallet is received, a task is created and the relevant fields are:
TaskType = REC, TaskStatus = AVL
When this rec. task is completed, the relevant fields become:
TaskType = REC, TaskStatus = CPS, CompleteQty = (Actual # of cases on the pallet), CompleteTime = (Actual time the task was completed) and PackID = a unique number assigned to the pallet that will never be reused for another pallet, but will be identified in all future tasks.
Now, from time to time, things are received incorrectly, miscounted, whatever - and the pallet needs to be "Unreceived". The resulting task will be:
TaskType = UNREC, TaskStatus = CPS, CompleteQty = (The number of cases unreceived from the pallet), CompleteTime = (Time this task was completed) and PackID = PackID that was assigned during the REC task that identifies the pallet.
Generally there are two scenarios here:
I receieve a pallet with, say 80 cases on it, and then have to unreceive the entire 80 cases and thus the entire pallet and the result is a REC that adds 80 cases/1 pallet and an UNREC that removes 80 cases/1 Pallet and thus no pallet is left in inventory.
Another scenario is that I receive 80 cases but it was a miscount and I have to unreceive 20 cases - so I still have that pallet/packID in inventory. The result is REC 1 pallet/80 cases, UNREC 20 cases = 1 remaining pallet with 60 cases on it.
The third scenario is that I REC a pallet with 80 cases and then have MULTIPLE UNREC tasks that delete the entire pallet.
For instance, REC 1 Pallet/80 Cases, UNREC 30 cases (and still have a pallet), another UNREC 30 cases (still have a pallet), a third UNREC 20 cases (and now I have no pallet) and I'm now left with a total UNREC of 80 Cases and 0 Pallets.
I need to write a dax formula that aggregates the UNREC QTY for EACH pack ID and compares it to the REC QTY for that pack ID and then counts all of the PackIDs that are depleted to 0.
Is this even possible? I've tried so many things.....
