Forum Discussion

RenierP's avatar
RenierP
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

v-dineshya's avatar
v-dineshya
Community Support
1 year ago

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.

RenierP's avatar
RenierP
Frequent Visitor
1 year ago

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