Days Vending machine non operating

[hanyfouda2023]

Hi helpers,

I have list of vending machines & database is contains Master tables for serial numbers & Sales tables contains "Sales , Qty"  I'm facing an issue that if one vending machine doesnt  work for one or several days i didnt dicover it.

So I need to know how to check if single or more vending machines not working

[hanyfouda2023]

Hi Daniel29195,

Thanks for your replay I tried it but it doesnt work , I need something like how many days this VM operating 

[Daniel29195]

@hanyfouda2023 

are you able to share some data .

with a visual representation maybe on excel, of the desired output. 

this would help  understanding more your requirement. 

[hanyfouda2023]

Here you are,

Master

Serial #
1
2
3
4
5
6
7
8
9
10
11
12

Transaction

Serial #	Date	Amount	Qty
1	01-Jan	10	1
2	01-Jan	20	2
3	01-Jan	50	5
4	01-Jan	67	7
5	01-Jan	87	3
6	01-Jan	107	6
7	01-Jan	127	7
8	01-Jan	147	8
9	01-Jan	167	9
10	01-Jan	187	10
11	01-Jan	207	11
1	02-Jan	227	12
2	02-Jan	247	13
3	02-Jan	267	14
4	02-Jan	287	14
5	02-Jan	307	15
6	02-Jan	327	16
7	02-Jan	347	17
8	02-Jan	367	18
9	02-Jan	387	19
1	03-Jan	407	20
2	03-Jan	427	21
3	03-Jan	447	22
4	03-Jan	467	23
5	03-Jan	487	23
6	03-Jan	507	24
7	03-Jan	527	25
8	03-Jan	527	26

[Daniel29195 · Accepted solution]

please follow these steps : 

 step 1 

create datetable table : 

datetable = CALENDAR(MIN(transactions[Date ]), MAX(transactions[Date ]))





sample data used : 

step 2 : 

link datetable new the transaction table via date column. 

step3 : create this measure : 

non working days =

var dates = ALL(datetable[Date])

var available_dates =

CALCULATETABLE(

    VALUES(transactions[Date ]),

    ALLSELECTED(datetable[Date])

)

var result =

EXCEPT(

    dates,

    available_dates

)

return  

CALCULATE(

    COUNTROWS(datetable),

    KEEPFILTERS(result)

)

step 4 :  create the visual 

drag the following columns the visual .

final result : 
this will gives the non working hours per #serial .

hope this helps .

best regards

[hanyfouda2023]

Thanks Alot for your support , it works fine!

[Daniel29195]

@hanyfouda2023 

[hanyfouda2023]

All I need to know how can I discover that Serial # 11 , 12 non performing in 02n January & serial numbers 9 , 10 , 11 non performing in 03rd January

[Daniel29195]

hello @hanyfouda2023 

try using the show items with no data.

this will show rows even if they don have values