Skip to main content
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
technolog
Super User

Model setup

  1. Create a Date table that covers the full range of your data and mark it as a date table.
  2. Relate Date to Sales In by the date column and to Sales Out by the date column. Use single direction from Date to each fact.
  3. Relate Product to all three tables. Relate Reseller to all three tables.
  4. Initial Stock table holds one row per reseller and product with the quantity on 2020 01 01.

Measures

Sales In Qty =
SUM('Sales In'[Qty])

Sales Out Qty =
SUM('Sales Out'[Qty])

Net Movement =
[Sales In Qty] + [Sales Out Qty] * COS(PI())

Initial Stock Qty =
COALESCE(SUM('Initial Stock'[Qty]), 0)

Cumulative Net Movement since 2020 01 01 =
VAR Anchor = DATE(2020, 1, 1)
RETURN
CALCULATE(
[Net Movement],
FILTER(
ALLSELECTED('Date'[Date]),
'Date'[Date] >= Anchor && 'Date'[Date] <= MAX('Date'[Date])
)
)

Stock On Hand by date =
[Initial Stock Qty] + [Cumulative Net Movement since 2020 01 01]

Stock at month end for visuals grouped by month =
VAR LastDay = EOMONTH(MAX('Date'[Date]), 0)
RETURN
CALCULATE(
[Stock On Hand by date],
KEEPFILTERS('Date'[Date] = LastDay)
)

 

View solution in original post