Forum Discussion
need help this calculated table by using dax
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]
)