Forum Discussion

hanyfouda2023's avatar
hanyfouda2023
Helper II
2 years ago
Solved

Days Vending machine non operating

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  wor...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

     

    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's avatar
hanyfouda2023
Helper II
2 years ago

Here you are,

Master

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

12

 

Transaction

Serial #Date AmountQty
101-Jan               10                  1
201-Jan               20                  2
301-Jan               50                  5
401-Jan               67                  7
501-Jan               87                  3
601-Jan             107                  6
701-Jan             127                  7
801-Jan             147                  8
901-Jan             167                  9
1001-Jan             187               10
1101-Jan             207               11
102-Jan             227               12
202-Jan             247               13
302-Jan             267               14
402-Jan             287               14
502-Jan             307               15
602-Jan             327               16
702-Jan             347               17
802-Jan             367               18
902-Jan             387               19
103-Jan             407               20
203-Jan             427               21
303-Jan             447               22
403-Jan             467               23
503-Jan             487               23
603-Jan             507               24
703-Jan             527               25
803-Jan             527               26
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

 

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