Historical Daily calculations based on dynamic fields

[dbrandone]

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:

_CurrentInventory =
     CALCULATE(
          COUNTROWS(TestData),
          NOT(ISBLANK(TestData[OrderCheckInDate])),
          TestData[ReturnShipStatus] = "Pending",
          OR(NOT(TestData[OrderStatus] = "Cancelled"),
                 NOT(TestData[OrderCheckInStatus] = "Cancelled"))
)

The issue I am running into is that the order is in house and sits in the warehouse for 2 days, I need that order to calculate in house for those two days and just not be counted based on the Shipping date (if I used that date in a calculation as the filter)

Any ideas?

Below is a google drive link to a .pbix file with data for you to look at. Any help is appreciated

https://drive.google.com/file/d/1MgtDjIaXZZpMhSkUOFxcoeYFIdVzaYr8/view?usp=sharing

[lbendlin]

Are you maybe using Power BI for something it was not designed for?  Don't you have an inventory management system in your company?

[dbrandone]

@lbendlin 

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.

[lbendlin]

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.

[dbrandone]

@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?

[lbendlin]

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?

[dbrandone]

@lbendlin 

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

[lbendlin]

"by creating a write back to SQL process"

CAUTION:  There is no guarantee that Power Query will run your query only once.  https://blog.crossjoin.co.uk/2020/07/05/why-is-power-bi-running-my-sql-query-twice/ 

Power BI does not support proper data write-back yet.

[dbrandone]

@lbendlin 

yeah, I figured that into the query I wrote. I have it check for duplicate entries first and if no duplicate found, then write. Else, just return the table without writing.