need help this calculated table by using dax

[tkavitha911]

Could you please help me create a calculated table in Power BI using DAX?
I have two tables: Calendar and Inbound_Query. The Inbound_Query table includes the following columns: Market, Supply Category, Container Number, Max ETA Date, Clearance Days, and Capacity.

I want to calculate a Predicted Delivery Date based on the following logic:

For each day and supply category, count the number of containers.
If the container count exceeds the capacity, the predicted delivery date should be: Max ETA Date + Clearance Days + 1 day.
Otherwise, it should be: Max ETA Date + Clearance Days.

I need a Remaining Containers column that tracks the number of containers exceeding the capacity for each day and market. These excess containers should be carried over to the next day. If the container count is within capacity, the remaining containers should be 0.

The final calculated table should include the following columns:

Market
Supply Category
Max ETA
Container Count
Remaining Containers
Capacity
Clearance Days
Predicted Delivery Date

[v-vpabbu]

Hi @tkavitha911,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @tkavitha911,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[tkavitha911]

Need to fixpending containers and Total containers pls help 
im providing the sample data and out put both 

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

[v-vpabbu]

Hi @tkavitha911,

Blank values in your input columns do affect measures, especially when doing cumulative or row-by-row logic.

Attached pbix file for your reference

Regards,

Vinay Pabbu

[Jihwan_Kim]

Hi, 

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result table = 
	VAR _condition = SELECTCOLUMNS(
		SUMMARIZE(
			ADDCOLUMNS(
				ADDCOLUMNS(
					SUMMARIZE(
						inbound_query,
						inbound_query[market],
						inbound_query[supply_category],
						inbound_query[max_eta],
						inbound_query[capacity]
					),
					"@container_count", CALCULATE(COUNTROWS(DISTINCT(inbound_query[container_number])))
				),
				"@condition", IF(
					inbound_query[capacity] < [@container_count],
					1,
					0
				),
				"@remaining_containers_count", IF(
					[@container_count] - inbound_query[capacity] > 0,
					[@container_count] - inbound_query[capacity],
					0
				)
			),
			inbound_query[market],
			inbound_query[supply_category],
			inbound_query[max_eta],
			[@container_count],
			[@remaining_containers_count],
			[@condition]
		),
		"@m", inbound_query[market],
		"@s", inbound_query[supply_category],
		"@container_count", [@container_count],
		"@remaining_containers_count", [@remaining_containers_count],
		"@maxeta", inbound_query[max_eta],
		"@condition", [@condition]
	)
	VAR _t = GENERATE(
		inbound_query,
		FILTER(
			_condition,
			[@m] = inbound_query[market] && [@s] = inbound_query[supply_category] && [@maxeta] = inbound_query[max_eta]
		)
	)
	RETURN
		SUMMARIZE(
			ADDCOLUMNS(
				SUMMARIZE(
					_t,
					inbound_query[market],
					inbound_query[supply_category],
					inbound_query[max_eta],
					[@container_count],
					[@remaining_containers_count],
					inbound_query[capacity],
					inbound_query[clearance_days],
					[@condition]
				),
				"@predicted_delivery_date", inbound_query[max_eta] + inbound_query[clearance_days] + [@condition]
			),
			inbound_query[market],
			inbound_query[supply_category],
			inbound_query[max_eta],
			[@container_count],
			[@remaining_containers_count],
			inbound_query[capacity],
			inbound_query[clearance_days],
			[@predicted_delivery_date]
		)

[tkavitha911]

The balance containers need to be carried forward to the next day, even though the input data does not have continuous dates

[v-vpabbu]

Hi @tkavitha911,

As per my analysis, the solution provided by @Jihwan_Kim  matches the requirement.

However, to assist you further and provide an accurate and working solution, could you please help clarify a few things:

1. Date Context: Is the expected output a daily view across a calendar, or are we only interested in dates that appear in the Max ETA date column from the Inbound Query table?
2. When you say you want "carried forward to the next day," in which column do you want the day to be carried forward?
3. Please provide a sample data/ pbix to get more clarity about your situation.

Your explanation will give us more clarity and hence we would be able to assist you better on this.

Regards,

Vinay Pabbu

[bhanu_gautam]

@tkavitha911 , Try using

CalculatedTable =
VAR CalendarTable = ADDCOLUMNS(
CROSSJOIN(
VALUES(Calendar[Date]),
VALUES(Inbound_Query[Supply Category])
),
"Market", Inbound_Query[Market],
"Max ETA", Inbound_Query[Max ETA Date],
"Clearance Days", Inbound_Query[Clearance Days],
"Capacity", Inbound_Query[Capacity]
)
VAR ContainerCountTable = ADDCOLUMNS(
CalendarTable,
"Container Count",
CALCULATE(
COUNTROWS(Inbound_Query),
FILTER(
Inbound_Query,
Inbound_Query[Supply Category] = EARLIER(CalendarTable[Supply Category]) &&
Inbound_Query[Market] = EARLIER(CalendarTable[Market]) &&
Inbound_Query[Max ETA Date] = EARLIER(CalendarTable[Date])
)
)
)
VAR PredictedDeliveryTable = ADDCOLUMNS(
ContainerCountTable,
"Remaining Containers",
VAR CurrentDate = CalendarTable[Date]
VAR CurrentMarket = CalendarTable[Market]
VAR CurrentSupplyCategory = CalendarTable[Supply Category]
VAR CurrentCapacity = CalendarTable[Capacity]
VAR CurrentContainerCount = ContainerCountTable[Container Count]
VAR PreviousRemainingContainers =
CALCULATE(
SUMX(
ContainerCountTable,
ContainerCountTable[Remaining Containers]
),
FILTER(
ContainerCountTable,
ContainerCountTable[Date] = CurrentDate - 1 &&
ContainerCountTable[Market] = CurrentMarket &&
ContainerCountTable[Supply Category] = CurrentSupplyCategory
)
)
VAR TotalContainers = CurrentContainerCount + PreviousRemainingContainers
RETURN
IF(
TotalContainers > CurrentCapacity,
TotalContainers - CurrentCapacity,
0
),
"Predicted Delivery Date",
IF(
ContainerCountTable[Remaining Containers] > 0,
CalendarTable[Max ETA] + CalendarTable[Clearance Days] + 1,
CalendarTable[Max ETA] + CalendarTable[Clearance Days]
)
)
RETURN
SELECTCOLUMNS(
PredictedDeliveryTable,
"Market", [Market],
"Supply Category", [Supply Category],
"Max ETA", [Max ETA],
"Container Count", [Container Count],
"Remaining Containers", [Remaining Containers],
"Capacity", [Capacity],
"Clearance Days", [Clearance Days],
"Predicted Delivery Date", [Predicted Delivery Date]
)