cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculate forecast of shipments of next 6 weeks

Hi!,

 

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

 

These are the logistic scenarios:

Capture.PNG

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:

 

Capture.PNG

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:

Capture2.PNG

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!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Calculate forecast of shipments of next 6 weeks

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Calculate forecast of shipments of next 6 weeks

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Calculate forecast of shipments of next 6 weeks

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

Highlighted
Super User IV
Super User IV

Re: Calculate forecast of shipments of next 6 weeks

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper II
Helper II

Re: Calculate forecast of shipments of next 6 weeks

@parry2k thank you!!

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors