urgent help needed

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.