Need advice from the group on how to come up with this measure.
I have the fact for the Purchase transactions in Purch Fact with Received qty by date and the qty which are on dock( To be considered as received)
PurchaseSnapshot have the snapshot taken at the end of the day for what is to be received. Date is the date that PO will be received and the snapshot date is the time of the snapshot job. So if there is a PO that is in the system that is going to be received in the next 6 days, the PO line will be in this table for each day the snapshot is taken. An example is PO 100103 with date of Dec 20, will be showing up for each snapshot taken on 17th,18th & 19th.
The report need to calculate the Total qty of receipts based on the date selected. The totals are a bit tricky.
Example - If the selected date is Dec 17, Total receipts should show the total receipt up to Dec 17 and the totals for On Dock for the rest of the month also ( until Dec 31 ). I have this measure in the model.
My issue is
- I need to calculate the difference of Receipts from yesterday with below conditions.
If selected date is Monday, need to calculate difference between Monday & Friday.
If selected date is first day of month, need to show total as total for the month or take previous day
total as 0.
If selected date is Dec 17th. result should be
Pbix file attached.
PO Test.pbix (77.1 KB)