Forum Discussion
Historical Daily calculations based on dynamic fields
Hi everyone,
I ran into a roadblock with one request I received from a department. I wanted to see if another set of eyes could shed some light on the possibilities.
The department needs to track daily inventory overnight that was not shipped out. The goal for the department is to receive a shipment, prep the object, and then ship out same day. The data has several date columns ranging from order date, checkindate(date received onsite), prep date, and packaged date (ships same day as packaged). the various steps have status columns as well. The issue I am having is that I can calculate real time, the inventory we have onsite, but once the database is updated and the item now obviously show offsite. The department wants to see the percentage of items shipped each day versus the items in inventory at beginning of the day plus the items received that day.
An example would be, if we have 50 items in inventory overnight and receive 50 items today, then the goal is to ship 90 items (90%) today. We need to be able to view these percentages WTD, MTD, and YTD based on the daily calculations.
The CheckIn date signifies that the item is on-premises, so my calculation to see what is onsite now is:
CALCULATE(
COUNTROWS(TestData),
NOT(ISBLANK(TestData[OrderCheckInDate])),
TestData[ReturnShipStatus] = "Pending",
OR(NOT(TestData[OrderStatus] = "Cancelled"),
NOT(TestData[OrderCheckInStatus] = "Cancelled"))
)
8 Replies
Are you maybe using Power BI for something it was not designed for? Don't you have an inventory management system in your company?
I see what you are saying, but unfortunately we do not in the context of what you are talking about. I have no access to the backend of this database and only can pull data from it. The department I am working with wants to track on a daily basis the percentage of orders are shipped with the denominator being inventory held over from previous day plus incoming orders that day.Since all orders for purpose of calculation are filtered by a date column, I was trying to figure a way to have an order be counted each day between received and shipped date. Example: if an order was received on 5/5(ReceivedDate column) and then ships on 5/8 (shippeddate column), I would need the measure or measure and calc column account for 5/6 and 5/7 in inventory but not specifically designated by a date column.
hope that makes sense. Believe me, I understand that 1. This data source and the process as a whole is nowhere near ideal, but it is what I have to work worth. 2. That this may not be possible. I have calculated the metric for in house currently and orders shipped today, but unsure of historical time related metrics in this situation.
The usual approach is to have one calendar table, and a fact table with multiple dates. One date is linked to the calendar date with an active relationship (say, OrderCreated Date). The other dates are linked via inactive relationships. These can then be used in measures via USERELATIONSHIP().
Your example seems to suggest that you want to use multiple calendar tables that cover different events. If you can let go of that design and move to the one I mentioned above then you may have a chance to make this work.
