Forum Discussion
Historical Daily calculations based on dynamic fields
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.
lbendlin
Due to the highly sensitive nature of the data we work with, the dataset that I sent is watered down and limited versus the data that I am actually playing with. I do use an extensive Date Table and all of the dates are connected to the date table and when I need to use a particular date that is not the primary connection to the date table, I do use "USERELATIONSHIP". So, what I am using on the backend does mimic somewhat of what you are describing.I do not mind going back to the department and telling them that their datasource design is not condusive to this particular metric, but wanted to at least reach out the experts first to see if it is possible. If you need me to add my date table to the example, then I can do that.
I have written a few measures that play off of SELECTEDVALUE ('Date' [Date]) within a table view but I still run into the each row only being counted once even if it is in inventory for 3 days. I understand why this is happening, but unsure if there is a way to do it. I was thinking that maybe using Power Query to build an inventory table with each order and the dates it is in inventory, but that would make the OrderId and the date column of that table a "Many" situation with both and don't know if that would work with this situatiuon. Thoughts?
