Hi Team, could you pls help me to create this calculated table by using dax

[tkavitha911]

Column Names: Date, Max ETA Date, Market, Container Number, Capacity

Metrics to Calculate:

1. Number of Containers Delivered (Daily):
   Every day, only a limited number of containers can be delivered based on the available capacity.

   • Example: If the capacity is 5, then only 5 containers can be delivered that day.

2. Number of Containers Pending Delivery (Daily):
   This is the number of containers that are still waiting to be delivered.

   • Formula:
     Pending = Total Containers - Delivered
     This includes containers from previous days that weren’t delivered due to capacity limits.

3. Total Containers (Daily):
   This is the sum of:

   • Containers that were pending from the previous day, and
   • New containers added on the current day.
   • Formula:
     Total = Previous Day's Pending + Today's New Containers
     
     sample out put
     

     Market	Date	Max eta date	No of containers arrived	Capacity	No of containers deliverd	pending	Total
     India	11-11-2024	11-11-2024	23	5	5	18
     India	12-11-2024	12-11-2024	24	5	5	37	42
     India	13-11-2024			5	5	32	37
     India	14-11-2024			5	5	27	32
     India	15-11-2024			5	5	22	27
     India	16-11-2024			5	5	17	22
     India	17-11-2024			5	5	12	17
     India	18-11-2024			5	5	7	12
     India	19-11-2024			5	5	2	7
     India	20-11-2024	20-11-2024	3	5	5	0	5
     India	21-11-2024	21-11-2024		5	5	-5	0
     India	22-11-2024	22-11-2024	5	5	5	-5	0
     India	23-11-2024	23-11-2024		5	5	-10	-5
     India	24-11-2024	24-11-2024		5	5	-15	-10

[v-hashadapu]

Hi @tkavitha911 , Thank you for reaching out to the Microsoft Community Forum.

Based on your description, I took some sample data and worked out the solution.

Please refer attached .pbix file for reference and share your thoughts.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[techies]

Hi @tkavitha911 have split the logic into parts by creating intermediate calculated tables, please try this

first create a base weekday table

BaseWeekdayTable =

FILTER (

    ADDCOLUMNS (

        SUMMARIZE (

            Containers,

            Containers[Market],

            Containers[Date],

            Containers[Max ETA Date]

        ),

        "NoOfContainersArrived",

            CALCULATE (

                COUNTROWS ( Containers ),

                ALLEXCEPT ( Containers, Containers[Market], Containers[Date] )

            ),

        "Capacity",

            CALCULATE (

                MAX ( Containers[Capacity] ),

                ALLEXCEPT ( Containers, Containers[Market], Containers[Date] )

            )

    ),

    WEEKDAY ( [Date], 2 ) <= 5  // Monday to Friday

)

Then add row numbers to sort --- calculate table

WeekdayWithRowNumber =

ADDCOLUMNS (

    BaseWeekdayTable,

    "RowNumber",

        RANKX (

            FILTER (

                BaseWeekdayTable,

                [Market] = EARLIER ( [Market] )

            ),

            [Date],

            ,

            ASC

        )

)

this is the final table

DeliverySummaryWeekdays final =

ADDCOLUMNS (

    WeekdayWithRowNumber,

    "Total",

        VAR CurrentMarket = [Market]

        VAR CurrentRow = [RowNumber]

        VAR PrevRows =

            FILTER (

                WeekdayWithRowNumber,

                [Market] = CurrentMarket &&

                [RowNumber] < CurrentRow

            )

        VAR PrevPending =

            SUMX (

                PrevRows,

                VAR Arr = [NoOfContainersArrived]

                VAR Cap = [Capacity]

                VAR Delivered = MIN ( Arr, Cap )

                RETURN Arr - Delivered

            )

        RETURN [NoOfContainersArrived] + PrevPending,

    "No of Containers Delivered",

        VAR CurrentMarket = [Market]

        VAR CurrentRow = [RowNumber]

        VAR PrevRows =

            FILTER (

                WeekdayWithRowNumber,

                [Market] = CurrentMarket &&

                [RowNumber] < CurrentRow

            )

        VAR PrevPending =

            SUMX (

                PrevRows,

                VAR Arr = [NoOfContainersArrived]

                VAR Cap = [Capacity]

                VAR Delivered = MIN ( Arr, Cap )

                RETURN Arr - Delivered

            )

        VAR TotalToday = [NoOfContainersArrived] + PrevPending

        RETURN MIN ( TotalToday, [Capacity] ),

    "Pending",

        VAR CurrentMarket = [Market]

        VAR CurrentRow = [RowNumber]

        VAR PrevRows =

            FILTER (

                WeekdayWithRowNumber,

                [Market] = CurrentMarket &&

                [RowNumber] < CurrentRow

            )

        VAR PrevPending =

            SUMX (

                PrevRows,

                VAR Arr = [NoOfContainersArrived]

                VAR Cap = [Capacity]

                VAR Delivered = MIN ( Arr, Cap )

                RETURN Arr - Delivered

            )

        VAR TotalToday = [NoOfContainersArrived] + PrevPending

        VAR DeliveredToday = MIN ( TotalToday, [Capacity] )

        RETURN TotalToday - DeliveredToday

)

[tkavitha911]

first table working fine but second table bit confusion about this WeekdayWithRowNumber

[techies]

Hi @tkavitha911 this will just show row number, for looping purposes, like this

[bhanu_gautam]

@tkavitha911 , Create measures using

// Calculate the total containers for each day
Total Containers =
VAR PreviousDayPending =
CALCULATE(
SUM('Table'[Pending]),
FILTER('Table', 'Table'[Date] = EARLIER('Table'[Date]) - 1)
)
RETURN
IF(
ISBLANK(PreviousDayPending),
'Table'[No of containers arrived],
PreviousDayPending + 'Table'[No of containers arrived]
)

// Calculate the number of containers delivered each day
No of Containers Delivered =
MIN('Table'[Capacity], 'Table'[Total Containers])

// Calculate the number of containers pending delivery each day
Pending =
'Table'[Total Containers] - 'Table'[No of Containers Delivered]

Create a calculated column for each metric:

Total Containers =
VAR PreviousDayPending =
CALCULATE(
SUM('Table'[Pending]),
FILTER('Table', 'Table'[Date] = EARLIER('Table'[Date]) - 1)
)
RETURN
IF(
ISBLANK(PreviousDayPending),
'Table'[No of containers arrived],
PreviousDayPending + 'Table'[No of containers arrived]
)

No of Containers Delivered =
MIN('Table'[Capacity], 'Table'[Total Containers])

Pending =
'Table'[Total Containers] - 'Table'[No of Containers Delivered]

[tkavitha911]

Need calculated table for only week days