Inventory on date

[CHL]

Hi,

I have a table with inventory movements like below:

Stock ID	Time In	Time Out	Movement Count
12345	01/01/2023 04:20	01/02/2023 17:00	1
12643	02/01/2023 18:00	02/01/2023 21:00	1
57543	01/01/2023 15:00	03/01/2023 14:21	1

I have a master calendar setup.

I need to report a count of 'stock' at midnight every day, that being stock that entered before midnight and left after midnight.

So based on the example above:

Date	Count
01/01/2023	2
02/01/2023	1
03/01/2023	0

I attempted to chart this using my Calendar date as the dimension and this measure:

SUM({<Date = {"<=$(=DATE(MIN([Time In])))>=$(=DATE(MAX([Time Out])-1))"}>} [Movement Count])

and although returning values they are not correct.

Is there a way?!

Thanks

[vinieme12]

Your dataset needs transformation to be able to evaluate this 

refer below

temp:
Load
*
,date(StockDate+iterno()-1) as StockDate2
While  StockDate+iterno()-1 <= (StockDate+if(StockDays=0,1,StockDays)-1)
;
load StockID
,TimeIn
,TimeOut
,Date(floor(Timestamp#(TimeIn,'DD/MM/YYYY hh:mm'))) as StockDate
,MovementCount
,floor(Timestamp#(TimeOut,'DD/MM/YYYY hh:mm'))-Floor(timestamp#(TimeIn,'DD/MM/YYYY hh:mm')) as StockDays
inline [
StockID,TimeIn,TimeOut,MovementCount
12345,01/01/2023 04:20,02/01/2023 17:00,1
12643,02/01/2023 18:00,02/01/2023 21:00,1
57543,01/01/2023 15:00,03/01/2023 14:21,1
];

in chart use below expression

Count({<StockDays={">0"}>}Distinct StockID)