Forum Discussion
Historical Daily calculations based on dynamic fields
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?
It might work for smaller data volumes. Custom logic and scalability don't usually go well together.
Which business decisions is the report driving? Can the intelligence required for making these decisions be gleaned in some other way?
This metric is for one of the departments yearly goals. I understand I am pushing the limits, if not over, what Power BI can do, but the database that I have access to is what it is (For now!!).
I decided for a temporary work around by creating a write back to SQL process. I created the equivilant of the DAX measure in Power Query within a duplicated table just for this process. I then built a table in a SQL instance I have and when the report refreshes, if a duplicate is not found, then it writes the data I need, and then the data refreshes on the Power BI end to reflect it. It is working so far, so fingers crossed it holds up well until l find another solution within Power BI or on the database side(not holding my breath).
Thanks for your assistance with this