topic Re: Hi Team, could you pls help me to create this calculated table by using dax in DAX Commands and Tips

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

tkavitha911 — Mon, 16 Jun 2025 06:53:09 GMT

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

Metrics to Calculate:

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

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

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

bhanu_gautam — Mon, 16 Jun 2025 07:37:39 GMT

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]

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

tkavitha911 — Mon, 16 Jun 2025 09:44:10 GMT

Need calculated table for only week days

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

techies — Tue, 17 Jun 2025 04:50:44 GMT

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

)

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

tkavitha911 — Tue, 17 Jun 2025 06:06:05 GMT

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

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

techies — Tue, 17 Jun 2025 06:16:16 GMT

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

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

v-hashadapu — Tue, 17 Jun 2025 12:33:47 GMT

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.

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

v-hashadapu — Fri, 20 Jun 2025 11:53:49 GMT

Hi tkavitha911 , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!

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

v-hashadapu — Mon, 23 Jun 2025 11:10:56 GMT

Hi tkavitha911 , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" to help others in the community. Please let me know if you have any further questions!

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

v-hashadapu — Thu, 26 Jun 2025 16:18:51 GMT

Hello tkavitha911 , Just getting back to see if the shared details answered your question. If so, marking it as "Accept as Solution" would be greatly appreciated to guide others in the community. Feel free to reach out with any additional questions!