Forum Discussion
Aggregate across different timelines
Hi RenierP ,
Thank you for reaching out to the Microsoft Community Forum.
DAX measure
LastQty =
VAR CurrentSKU = SELECTEDVALUE(Stock[SKU])
VAR LastDate =
CALCULATE(
MAX(Stock[Date]),
FILTER(
ALL(Stock),
Stock[SKU] = CurrentSKU &&
Stock[Date] <= MAX(Calendar[Date])
)
)
RETURN
CALCULATE(
MAX(Stock[QTY]),
Stock[SKU] = CurrentSKU &&
Stock[Date] = LastDate
)
Note:
ALL(Stock) removes filters on the stock table to get a reliable max date. We re-apply the filter manually for the current SKU. It ensures we're always calculating the latest known stock per SKU, up to the current Calendar[Date].TotalStock Measure :
TotalStock =
SUMX(
VALUES(Stock[SKU]),
[LastQty]
)
Note : This will iterate over each SKU, get its last known quantity, and sum them.Use Calendar[Date] on the X-axis. Plot TotalStock. Add a slicer for SKU .
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
RenierP Ensure you have a calendar table that covers the entire date range of your data.
Ensure your Stock table is related to the Calendar table on the Date column.
Create a Measure for Last Non-Blank Value:
DAX
LastQty =
VAR LastDate =
CALCULATE(
MAX(Stock[Date]),
FILTER(
Stock,
Stock[Date] <= MAX(Calendar[Date])
)
)
RETURN
CALCULATE(
MAX(Stock[QTY]),
Stock[Date] = LastDate
)
Create a Measure to Sum Quantities:
DAX
TotalStock =
SUMX(
VALUES(Stock[SKU]),
[LastQty]
)
Use the Calendar[Date] as the axis and the TotalStock measure as the value to plot the stock trend over time.
Hi Bhanu,
Thank you for your reply. Unfortunately this did not quite do the trick. When I filter on the page to a single SKU, it works as expected, but so does a simple sum on the quantity.
But when you select 2 or more, the addition does not work