inventory last snapshot not all days

[Norbertus]

Hi,

we have the next problem.

Several times a year, not every day or month, we count the products in a warehouse.

This inventory table is connected to a date calendar.

Now we i want to know for each day, month, quarter of a year what the count of the products is.

But if we don't have a inventory in example March we need the last value before.

I tried a lot of examples, but the most asume that there is a frequent inventory. In our situation we get a "zero" and we need the last value

[LivioLanzo]

Hi @Norbertus

are you able to post a data sample?

[Norbertus]

Inventory date	Quantity
1-1-2018	500
15-1-2018	550
1-6-2018	400
1-8-2018	300

This should be the result

Period	In stock
januari	550
februari	550
maart	550
april	550
mei	550
juni	400
juli	400
augustus	300
september	300
oktober	300
november	300
december

[LivioLanzo · Accepted solution]

Hello @Norbertus

I am assuming you already have such model in place:

I have added a calculated column in the Calendar Table with this formula:

HasInventoryEntry = 
NOT( ISEMPTY( RELATEDTABLE( Inventory ) ) )

And then used this measure in the Matrix:

Quantity Balance  = 
VAR mxDte = MAX( 'Calendar'[Date] )
RETURN
CALCULATE(
    SUM( Inventory[Quantity] ),
    CALCULATETABLE(
        LASTDATE( 'Calendar'[Date] ),
        'Calendar'[HasInventoryEntry] = TRUE(),
        'Calendar'[Date] <= mxDte
    )
)

If the inventory can have different categories, like for instance North and South, then you can do it like this:

Quantity Balance = 
VAR mxDte =
    MAX ( 'Calendar'[Date] )
RETURN
SUMX(
    VALUES( Regions[Region] ),
        CALCULATE (
            SUM ( Inventory[Quantity] ),
            LASTNONBLANK (
                CALCULATETABLE (
                    VALUES ( 'Calendar'[Date] ),
                    'Calendar'[HasInventoryEntry] = TRUE (),
                    'Calendar'[Date] <= mxDte
                ),
                CALCULATE ( COUNTROWS ( Inventory ) )
            )
        )
)

[Norbertus]

@LivioLanzo,

great thank you! 

[LivioLanzo]

Hello @Norbertus

I am assuming you already have such model in place:

I have added a calculated column in the Calendar Table with this formula:

HasInventoryEntry = 
NOT( ISEMPTY( RELATEDTABLE( Inventory ) ) )

And then used this measure in the Matrix:

Quantity Balance  = 
VAR mxDte = MAX( 'Calendar'[Date] )
RETURN
CALCULATE(
    SUM( Inventory[Quantity] ),
    CALCULATETABLE(
        LASTDATE( 'Calendar'[Date] ),
        'Calendar'[HasInventoryEntry] = TRUE(),
        'Calendar'[Date] <= mxDte
    )
)

If the inventory can have different categories, like for instance North and South, then you can do it like this:

Quantity Balance = 
VAR mxDte =
    MAX ( 'Calendar'[Date] )
RETURN
SUMX(
    VALUES( Regions[Region] ),
        CALCULATE (
            SUM ( Inventory[Quantity] ),
            LASTNONBLANK (
                CALCULATETABLE (
                    VALUES ( 'Calendar'[Date] ),
                    'Calendar'[HasInventoryEntry] = TRUE (),
                    'Calendar'[Date] <= mxDte
                ),
                CALCULATE ( COUNTROWS ( Inventory ) )
            )
        )
)