Cumulative total between date slicer

[temuulenchoi]

Hi there,

I am trying to filter a list of cumulative Inventory with a date slicer.
It works when I move the "until" side, but when I move the "from" side, it shows wrong numbers. 

Example "Sale" table which is used for calculating:

Item	InQty	OutQty	Date
A	20	2	2022/07/14
A		5	2022/07/15
A		4	2022/07/16
A		8	2022/07/17
A	15	3	2022/07/18
A		5	2022/07/19
A		2	2022/07/20
A	10	6	2022/07/21

I also have a Date Table which is used for the slicer and has relationship to the Sales table. 

Following measure is used:

Cumulative Total Inventory=

CALCULATE(

    SUM(Sale[InQty])-SUM(Sale[OutQty]),

    FILTER(ALLSELECTED(DateTable[Date]),

    DateTable[Date] <= max(DateTable[Date]) && max(DateTable[Date]) <= TODAY()))

The resulting table would be 

Cumulative Total inventory	Date
18	2022/07/14
13	2022/07/15
9	2022/07/16
1	2022/07/17
13	2022/07/18
8	2022/07/19
6	2022/07/20
10	2022/07/21

And the desired table, for example, between 2022/07/19 and 2022/07/21, would be:

Cumulative Total inventory	Date
8	2022/07/19
6	2022/07/20
10	2022/07/21

Thank you very much in advance!

[ryan_mayu]

@temuulenchoi 

what's the logic of calculating the inventory? It's because what i calculate is different from yours. Could you pls explain more on this?

[temuulenchoi]

I'm sorry, there must have been an error on copying the table. I just edited the post with the correct table. Thanks!

[ryan_mayu]

@temuulenchoi 

pls see the attachment and check if this is what you want.

[amitchandak · Accepted solution]

@temuulenchoi , You should use all as inventory need to build from start

Cumulative Total Inventory=
CALCULATE(
SUM(Sale[InQty])-SUM(Sale[OutQty]),
FILTER(ALL(DateTable[Date]),
DateTable[Date] <= max(DateTable[Date]) && (DateTable[Date]) <= TODAY()))

[temuulenchoi]

@amitchandak, now I'm trying to count the out of stock dates. Imagine if the inventory was 0 between 2022/07/25 and 2022/07/28, the following measure returns 4. 

Days out of stock =

COUNTROWS(

    FILTER(

        ADDCOLUMNS(

            CROSSJOIN( VALUES(DateTable[Date]), VALUES(Sale[Item])),

"Inventory Levels", [Cumulative Total Inventory]),

[Inventory Levels] = 0  && NOT(ISBLANK([Inventory Levels]))))

but if i start the slicer from 2022/07/26 for example, it returns blank. Could you help me with this? Thank you very much in advance!

[temuulenchoi]

Thanks! This worked very well