Forum Discussion
Aggregate across different timelines
Hi,
I have a system that records the qty in stock per material lot but only after a stock transaction. This means that a "slow-moving" SKU will only have a few qty recordings while a "fast-moving" SKU will have many. So now my question is how to construct a total stock count at a specific date to plot on a stock trend.
I have a common calendar table to produce a monotonous set of dates and this is related to my stock table.
So if there is a value for a specific SKU at a date then I must use that, if not I must find the last value I have for it. And then I need to sum across all SKUs for the specific date. Doing this manually is super simple, but I cannot figure out for the life of me how to do this in PowerBI. Note that I am taking about thousands of SKUs over a 10-year period so taking daily snapshots in the source system is not really an option and requires a rather efficient calculation method in PowerBI
In simple form, the data looks like
| Date | Warehouse | SKU | QTY |
| 2020/01/01 | WH01 | 123/456 | 100 |
| 2020/01/05 | WH01 | 123/456 | 90 |
| 2020/01/06 | WH01 | 123/456 | 110 |
| 2020/01/25 | WH01 | 123/456 | 100 |
| 2020/01/01 | WH01 | 789/321 | 50 |
| 2020/01/31 | WH01 | 789/321 | 60 |
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
8 Replies
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
Hi RenierP ,
Thank you for reaching out to the Microsoft Fabric Community forum.
Can you please follow below steps.
1. Calendar Table
Ensure your calendar table (Calendar) is connected to your Stock table via the Date column.2. Relationships
You should have:Calendar[Date] → Stock[Date] (many-to-one, single direction)
3. DAX Measure
Total Stock on Date =
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
SUMX(
VALUES('Stock'[SKU] & "|" & 'Stock'[Warehouse]),
VAR SKUWarehouse = SELECTCOLUMNS(VALUES('Stock'), "SKU", 'Stock'[SKU], "Warehouse", 'Stock'[Warehouse])
VAR LatestQty =
CALCULATE(
LASTNONBLANKVALUE(
'Stock'[Date],
CALCULATE(SUM('Stock'[QTY]))
),
FILTER(
ALL('Stock'),
'Stock'[SKU] = MAX('Stock'[SKU]) &&
'Stock'[Warehouse] = MAX('Stock'[Warehouse]) &&
'Stock'[Date] <= CurrentDate
)
)
RETURN
COALESCE(LatestQty, 0)
)If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.Thanks Dinesh but this also does not work. the line
VALUES('Stock'[SKU] & "|" & 'Stock'[Warehouse]),seems problematic.
Can you perhaps talk me through the steps you are applying and why? You declare a variable `SKUWarehouse` but I do not see it ever being used. Why?
Regards
Renier
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 youHi RenierP ,
Thank you for reaching out to the Microsoft Community Forum.
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
HI RenierP ,
Thank you for reaching out to the Microsoft Community Forum.
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
