Hi,
I always struggle with calculating where are no data points.
Scenario:
There Item Ledger entries for incoming and outgoing quantities, but not for every day. (Posting Date of this table is connected to calendar table - Many to One).
The first data entry is always the warehouse incoming product quantity.
Receipt Date =
CALCULATE(
MIN(ILE[Posting Date]),
FILTER(
ILE,
ILE[ItemLotNo] = EARLIER(ILE[ItemLotNo])
)
)
Based on this date, I calculate the days difference between the line date an the incoming date.
age in days =
DATEDIFF(
MIN(ILE[Receipt Date]),
MAX('Calendar'[Date]),
DAY
)
To get the stock per day, I cummulate the Item Ledger Entry Quantity.
Cumulative inventory =
CALCULATE(
SUM(ILE[Quantity]),
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
Now I want to calculate the costs per day based on a cost scale table (between these count of days, it cost that much).
Inventory costs per day =
VAR d = MAX ( 'Calendar'[Date] )
RETURN
SUMX (
SUMMARIZE (
ILE,
ILE[ItemLotNo],
"InDate", MIN ( ILE[Receipt Date] )
),
VAR _item = [ItemLotNo]
VAR _inDate = [InDate]
VAR _age = DATEDIFF ( _inDate, d, DAY )
// passenden Satz aus der Kostenstaffel suchen
VAR _rate =
MAXX (
FILTER ( 'Cost scale TEST', _age >= 'Cost scale TEST'[cost] && _age <= 'Cost scale TEST'[to] ),
'Cost scale TEST'[cost]
)
VAR _stockAsOfD =
CALCULATE (
[Cumulative inventory],
FILTER ( ALL ( ILE[Posting Date]), ILE[Posting Date] <= d ),
ILE[ItemLotNo] = _item
)
RETURN COALESCE ( _stockAsOfD, 0 ) * COALESCE ( _rate, 0 )
)
Problem now is that days where there is no item ledger entry are not calculated with the corresponding costs.
I cant find the problem here.
Thx for any advise!
PS: Example Data
Item Ledger Entry
Posting DateItemLotNoQuantityReceipt Date
|
Cost Scale
from dayto daycost per day
|
Calendar
Date
|
Hi,
thx for this information. I kind of understand the problem.
Now it is at least calculated for every single day, but there are no costs at all.
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
