Start and end of period inventory by slicer values

[CRN]

Hi all,

I'm trying to figure out a way to calculate the starting and ending inventory. Data of inventory is transactions (Item Ledger Entries from Business Central) as shown below.

I wan't to be able to slice the dates for a given period.

Then by the values of the slicer i need to calculate the sum of quantity until the first date of the slicer (SOP).

Further i need to calculate the sum of quantity until the last date of the slicer (EOP).

I've tried to visualize my request:

Thanks in advance!

[CRN]

Hi @johnt75,

Thanks for the recommendation! It works perfectly for now! Hopefully I'll be able to figure it out myself as the measure expands.

Thanks again!

[johnt75 · Accepted solution]

If you have a Date table linked to your fact table you could use

SOP =
VAR MinDate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] < MinDate
    )

EOP =
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] < MaxDate
    )