urgent help needed

[tkavitha911]

This is the daily data at the port, but since the dates are not continuous, I created a separate dates table. I need help writing a DAX measure to calculate the difference between the Max ETA Date and the Predicted Delivery Date for each market. If the difference exceeds the capacity, the delivery should be scheduled for the next day.

The expression will be: (Max ETA Date - Predicted Delivery Date). If the difference exceeds the available capacity, the remaining quantity should be carried over to the next day, and this cycle should continue until all deliveries are scheduled.

Market	Max of eta	Predicted_Delivery_Date	Max of eta	Capacity
Japan	01-02-2024'	04-01-1900	12-10-2024	2
Japan	01-02-2024'	04-01-1900	10-03-2025	2
Philippines	01-02-2024'	04-01-1900	10-04-2025	9
Vietnam	01-02-2024'	04-01-1900	04-10-2024	2
Taiwan	01-02-2024'	04-01-1900	19-04-2025	6
Thailand	01-02-2024'	04-01-1900	01-03-2025	7
Australia	01-02-2024'	04-01-1900	08-02-2025	6
China	01-02-2024'	04-01-1900	30-03-2025	8
Shenzhen	01-02-2024'	04-01-1900	23-03-2025	4
Korea	01-02-2024'	04-01-1900	29-11-2024	10
Vietnam	01-02-2024'	04-01-1900	21-03-2025	2
China	01-02-2024'	04-01-1900	21-03-2025	8
Japan	02-02-2024	04-01-1900	21-03-2025	2
Japan	03-02-2024	04-01-1900	21-03-2025	2
Philippines	04-02-2024	04-01-1900	21-03-2025	9
Vietnam	05-02-2024	04-01-1900	21-03-2025	2
Taiwan	02-02-2024	04-01-1900	04-04-2025	6
Thailand	03-02-2024	04-01-1900	04-04-2025	7
Australia	04-02-2024	04-01-1900	04-04-2025	6
China	05-02-2024	04-01-1900	30-03-2025	8
Shenzhen	06-02-2024	04-01-1900	04-04-2025	4
Korea	07-02-2024	04-01-1900	03-05-2025	10
Vietnam	08-02-2024	04-01-1900	21-08-2024	2
China	09-02-2024	04-01-1900	21-08-2024	8

[v-csrikanth]

Hi @tkavitha911 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

HI @tkavitha911 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @tkavitha911 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @tkavitha911 
Thank you for being part of the Microsoft Fabric Community.

1. The scenario involves distributing delivery quantities from the Predicted Delivery Date to the Max ETA Date, constrained by a daily Capacity per market.

2. If the total quantity to be delivered exceeds the daily capacity, the remaining quantity should be scheduled on subsequent available dates, even beyond the Max ETA if needed.

3. A separate Dates table is necessary to handle non-continuous date ranges and to ensure date-based calculations work properly.

4. The solution uses a calculated table in Power BI to simulate this delivery schedule. It:

   • Calculates total quantity using the date difference between Max ETA and Predicted Delivery Date.

   • Determines the number of days required to complete the delivery based on capacity.

   • Uses GENERATESERIES and RANKX on the Dates table to assign deliveries to actual dates.

   • Carries forward the remaining quantity to the next valid delivery date from the Dates table.

5. Here's the DAX for the calculated table:
   

   DeliverySchedule =
   VAR BaseTable =
   ADDCOLUMNS (
   'YourDataTable',
   "StartDate", [Predicted_Delivery_Date],
   "EndDate", [Max of eta],
   "TotalQty", DATEDIFF([Predicted_Delivery_Date], [Max of eta], DAY) + 1
   )

   RETURN
   GENERATE (
   BaseTable,
   VAR TotalQty = [TotalQty]
   VAR Capacity = [Capacity]
   VAR StartDate = [StartDate]
   VAR NumDays = CEILING (TotalQty / Capacity, 1)
   RETURN
   ADDCOLUMNS (
   GENERATESERIES (0, NumDays - 1, 1),
   "DeliveryDate",
   CALCULATE (
   MIN ( 'Dates'[Date] ),
   FILTER (
   'Dates',
   'Dates'[Date] >= StartDate &&
   RANKX (
   FILTER ( 'Dates', 'Dates'[Date] >= StartDate ),
   'Dates'[Date],
   ,
   ASC
   ) = [Value] + 1
   )
   ),
   "DeliveredQty",
   VAR Remaining = TotalQty - [Value] * Capacity
   RETURN IF (Remaining >= Capacity, Capacity, Remaining)
   )
   )

6. This table can now be used in your visuals (e.g., Gantt charts or line graphs) to display delivery progress over time per market.

7. The logic ensures that delivery respects the capacity and date availability, even if the deliveries need to go past the Max ETA Date.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[bhanu_gautam]

@tkavitha911 , Try using

dax
DeliverySchedule =
VAR MaxETADate = MAX('Table'[Max of eta])
VAR PredictedDeliveryDate = MAX('Table'[Predicted_Delivery_Date])
VAR Capacity = MAX('Table'[Capacity])
VAR DateDifference = DATEDIFF(PredictedDeliveryDate, MaxETADate, DAY)
VAR RemainingQuantity = DateDifference - Capacity

RETURN
IF(
DateDifference <= Capacity,
DateDifference,
RemainingQuantity
)