need help with Lastnonblank in inventory problem

[Anonymous]

I have a case as shown in the attached table. The red number is the inventorying amount. If the following month has no inventory then it should be like the last month, until a new inventory value and the following months will be the previous month value (the black italic). I would like to see the storage development as the chat shows.

I used:

InventoryAmount =
SUMX (
VALUES ( Table[Item);
CALCULATE (
SUM (Table[Amount]);
CALCULATETABLE(
LASTNONBLANK(
Table[InventoryMonth];
CALCULATE( COUNTROWS(Table) )
);
DATESBETWEEN(
Table[InventoryMonth];
BLANK();
Max(Table[InventoryMonth])
)
)
)
)

But I only get the red numbers column sum, not the black ones.

All helps are appreciated. Thanks in advance.

[Anonymous]

I have a case as shown in the attached table. The red number is the inventorying amount. If the following month has no inventory then it should be like the last month, until a new inventory value and the following months will be the previous month value (the black italic). I would like to see the storage development as the chat shows.

I used:

InventoryAmount =
SUMX (
VALUES ( Table[Item);
CALCULATE (
SUM (Table[Amount]);
CALCULATETABLE(
LASTNONBLANK(
Table[InventoryMonth];
CALCULATE( COUNTROWS(Table) )
);
DATESBETWEEN(
Table[InventoryMonth];
BLANK();
Max(Table[InventoryMonth])
)
)
)
)

But I only get the red numbers column sum, not the black ones.

All helps are appreciated. Thanks in advance.

[MFelix]

Hi @Anonymous,

Your measure is correct however in order to make it work you need to create a dimension table for Items and another one for the months.« your formula should look like this after that table is made and use on your matrix visual then add the following measure to your model:

InventoryAmount = 
SUMX (
    VALUES ( items[Item] );
    CALCULATE (
        SUM ( 'Table'[Amount] );
        CALCULATETABLE (
            LASTNONBLANK (
                'Table 2'[InventoryMonth];
                CALCULATE ( COUNTROWS ( 'Table' ) )
            );
            DATESBETWEEN (
                'Table 2'[InventoryMonth];
                BLANK ();
                MAX ('Table 2'[InventoryMonth] )
            )
        )
    )
) + 0

This will give you the result below:

Check the pbix file attach.

Regards,

MFelix

[Anonymous · Accepted solution]

Great!

I really appreaciate your quick and working solution!

Thanks super Datanaut!

Dav - a to be Datanaut