Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)