Creating a shipping schedule with on going date range changes

[Anonymous]

Hello,

I am relatively still new to Power BI with some basics in DAX and Excel. What I am trying to create is a shipping schedule to monitor daily inbound and outbound loads from multiple locations.

Below is a picture of some sample data that would be similar format to what I would be using. I have every location on a separate excel tab currently, hoping it would be easier to update in the future when I can just add the next month's numbers.

What would be the best way to approach this knowing that I want to montior daily shipments from various locations and be able to update min/maxes for inbound/outbound? (I don't want to change the dates each time so I can keep historical data to show, for example, when location A's max outbound was 5 loads per day in October, we met the goal 95% of the time but in December of that same year when it went to 15 loads per day outbound max.....we were 87% successful in hitting 15 load per day max)

I planned to create a table either way and add it to my query editor, but I don't know if each location should be a table itself or altogether and if I could get some assistance with the calculations as well(especially on how to account for changing whatever column/measure/etc each month)....that would much appreciated.

Thank you for your time in reading and I can provide more details, try to provide more sample data as needed

[v-xuding-msft]

Hi @Anonymous ,

Do you want to get the max outbound per day or per month?  Based on your description, I think you want to monitor daily shipments. For the sample data ,  the data is shown with per month.  For the separate files, you could append them into one table and the result will not be affected when calculate.

You could post your expected result that will make us understand clearly. And I created a sample which is appended queries and  show the max bound for per location.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-xuding-msft  for posting a reply and offering guidance

 

I would want to monitor daily outbound.

It looks like from your attached sample that you posted Max Outbound for each location in their latest month (December) Please correct me if I read it wrong.

Using your sample table, could I just create a slicer/drop down menu to show multiple months or only current month at a time? If that was the case, I think if I could show a actual shipment counts in a line clustered chart where the bars would be the predetermined inbound/outbound numbers and my line would be what the actual inbound/outbound numbers were.

Attached is a visual of what I tried to describe above( Profit in the graph would be my actual and Rainy Days would be the preset min/max set numbers). If it would be easier to flip them, that is completely fine too.