Calculate forecast of shipments of next 6 weeks

[pratafran]

Hi!,

I have the following case that I'm trying to figure out how to solve in PBI.

These are the logistic scenarios:

WH= warehouse

R= Railcar (equivalent to 4 trucks)

T=Truck

B/C/D= different warehouse names

There are

DIRECT shipments from origin to a final warehouse and

TRANSLOADING shipments that first go to a warehouse 1 and then, from warehouse 1 to warehouse 2.

I want to calculate the Forecast for next 6 weeks of each mean of transportation and for each Warehouse but considering that if a Railcar arrives to a warehouse 1, then it turns into 4 trucks to move to warehouse 2 from there.

These is an example table with the data:

ETA 1: Estimated Time of Arrival at first WH

ETA 2: Estimated Time of Arrival at second WH (if there is only one WH, it is the same than ETA 1)

I calculated the week of arrival considering Monday as first day of week and today's week as 0.

The forecast should show only shipments expected to arrive from week 0 to 6 (the orange ones must be omitted.

WIth that Data Table, this is the result I expect:

I was thinking in the following approach in Power Query within the BI

1) To generate two tables

      Direct shipments + the first leg of the transloading,

      Second one consdiering only the second leg shipments)

2) Calculate WeekNum vs today and filter only Week 0 to 6

3) Append both tables

4) generate the corresponding tables or visuals from there

The problem I have is that I was able to calculate the WEEKNUM ( ETA , 1 ) - WEEKNUM ( TODAY() , 1 ) in DAX, but then I was not able to do it in Power Query because it uses another language (M)... so I'm stuck!!!

Thanks!!!

[parry2k · Accepted solution]

@pratafran sorry for the delay on this. Based on your DAX expression, here is the alternate in Power Query to achieve the same.

Add custom column in Power Query with following M code , assuming column name in your table is called Date

Date.WeekOfYear([ETA]) - 
Date.WeekOfYear(Date.From(DateTime.LocalNow()))

 Would appreciate Kudos 🙂 if my solution helped.

[pratafran]

@parry2k thank you!!

[parry2k]

@pratafran I read your post and there is lot information, seems like at end of the day you are looking to solve following in Power Query, correct?

The problem I have is that I was able to calculate the WEEKNUM ( ETA , 1 ) - WEEKNUM ( TODAY() , 1 ) in DAX, but then I was not able to do it in Power Query because it uses another language (M)... so I'm stuck!!!

[pratafran]

Honestly this is an old project I gave up, but yes, we can simplify the issue by answering the last sentence 🙂