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

Hi Daniel29195,

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

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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'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


       
       
      • hanyfouda2023's avatar
        hanyfouda2023
        Helper II
        2 years ago

        Thanks Alot for your support , it works fine!

    • hanyfouda2023's avatar
      hanyfouda2023
      Helper II
      2 years ago

      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