Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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!!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Honestly this is an old project I gave up, but yes, we can simplify the issue by answering the last sentence 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |