Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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
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
Hi @tkavitha911,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,
Vinay Pabbu
Hi @tkavitha911,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this.
Regards,
Vinay Pabbu
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 |
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
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]
)
The balance containers need to be carried forward to the next day, even though the input data does not have continuous dates
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:
Your explanation will give us more clarity and hence we would be able to assist you better on this.
Regards,
Vinay Pabbu
@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]
)
Proud to be a Super User! |
|
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |