Forum Discussion

tkavitha911's avatar
tkavitha911
Helper III
1 year ago

need help this calculated table by using dax

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

urgent help needed

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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's avatar
      tkavitha911
      Helper III
      1 year ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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's avatar
    bhanu_gautam
    Super User
    1 year ago

    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]
    )