10 Replies Latest reply on Mar 21, 2018 9:33 AM by Mohamed Thahir Gaffar Hussain

    Count a measure and divide it by all business days?

    David Nilsson




      I can't share the whole dataset so I have tried to simplify it.


      I have a set of data with orders. Each order has a date.


      The data is built like this:






      I need to calculate how many orders each merchant has per business day the last two weeks. The problem is that when I filter out Saturdays and Sundays and  calculate "COUNT(IF datediff('day',[Date],[enter start date])<=13 THEN [Order] END) / Countd(date)"  I will get the amount of orders divided by the dates the merchant had orders on. not the total amount of days.


      ("Enter start date" is a parameter)



      So in this case I will get:


      Merchant A:

      5 orders / 4 days = 1,25 orders per day


      Merchant B:

      1 Order/ 1 day = 1 orders per day




      What I want to get:


      Merchant A:

      6 orders / 5 days = 1,2 orders per day


      Merchant B:

      1 Order/ 5 days = 0,2 orders per day



      (Maybe needless to say but of course I need this to be dynamical so I can't divide by "5")




      I have scratched my head, searched the web and these forums but I can't seem to find the answer. Would be so grateful if you could help me.


      I have attached a workbook if you need it.