Forum Discussion
Oddy
3 years agoFrequent Visitor
Deliveries + Inventory DAX Measure
Hey all - I have been struggling on this for days and tried everything that I could think of, but I can't seem to get it to work: OVERALL Purpose: This dashboard needs to calculate the Current Inve...
Oddy
3 years agoFrequent Visitor
This is the closest I have gotten:
Deliveries are being taken into account - stock is being taken into account - all works except:
The order data pieces are being put into the first month - not the right month.
_mProjected+Deliveries_test =
VAR AvailableDate = EOMONTH(TODAY(), -1) + 1
// Get the list of delivery dates that are active and related to the '_Calendar' table
VAR ActiveDeliveryDates =
CALCULATETABLE(
VALUES('Order Data'[Delivery_month]),
USERELATIONSHIP('_Calendar'[Date], 'Order Data'[Delivery_month])
)
// Find the maximum delivery date to consider deliveries up to the maximum date
VAR MaxDeliveryDate =
CALCULATE(
MAX('Order Data'[Delivery_month]),
ALL('Order Data'),
USERELATIONSHIP('_Calendar'[Date], 'Order Data'[Delivery_month])
)
// Filter the '_Calendar' table to include all months from AvailableDate up to MaxDeliveryDate
VAR CumulativeDeliveries =
FILTER (
ALL ( '_Calendar' ),
'_Calendar'[Date] >= AvailableDate
&& '_Calendar'[Date] <= MaxDeliveryDate
)
// Calculate the total deliveries for all months in CumulativeDeliveries
VAR Deliveries =
SUMX (
CumulativeDeliveries,
CALCULATE (
SUM ( 'Order Data'[Total Pieces] ),
USERELATIONSHIP('_Calendar'[Date], 'Order Data'[Delivery_month])
)
)
// Calculate the current month's quantity
VAR CurrentMonthQty =
CALCULATE(
SUM('Stock Data'[Quantity Available]),
FILTER(
ALL('_Calendar'),
YEAR('_Calendar'[Date]) = YEAR(AvailableDate) &&
MONTH('_Calendar'[Date]) = MONTH(AvailableDate)
)
)
// Calculate the number of remaining months from AvailableDate to the minimum date in '_Calendar' table
VAR MonthsDiff =
DATEDIFF(AvailableDate, MIN('_Calendar'[Date]), MONTH)
VAR RemainingMonths = MAX(0, MonthsDiff)
// Calculate the RunRate and use it as a negative value (decrease)
VAR RunRate = -1 * CALCULATE(
SELECTEDVALUE('Stock Data'[Run Rate]),
ALLEXCEPT('Stock Data', 'Stock Data'[SKU Title])
)
// Calculate the projected quantity based on the deliveries, current month's quantity, and RunRate
VAR Calc = CurrentMonthQty + Deliveries + (RunRate * RemainingMonths)
RETURN
ROUNDUP(Calc, 0)