5 Replies Latest reply on Aug 30, 2018 5:24 AM by Mahfooj Khan

    How to get sum of a measure based on maximum date within a selected time range ?

    Sukriti Surawdhaniwar

      I have a below dataset which is restricted with a filter provided between dates 12/01/2018 to 14/01/2018

       

      Name of StoreVisit dateNo of DevicesLatest visit dateCount of devices during latest visit
      Store -0112/01/2018214/01/20181+5=6
      Store -0113/01/2018314/01/20181+5=6
      Store -0114/01/2018114/01/20181+5=6
      Store -0212/01/2018314/01/20181+5=6
      Store -0213/01/2018214/01/20181+5=6
      Store -0214/01/2018514/01/20181+5=6

       

      I need to derive logic for column-3 and column-4 in the above dataset.

      Logic: For a particular store if the latest visit is 14th Jan within a selected filter range then it should count all the no.of devices for that latest visit date.

      For example, If now the range is changed to 12/01/2018 to 13/01/2018 then the result should be like below :

       

      Name of StoreVisit dateNo of DevicesLatest visit dateCount of devices during latest visit
      Store -0112/01/2018213/01/20183+2=5
      Store -0113/01/2018313/01/20183+2=5
      Store -0212/01/2018313/01/20183+2=5
      Store -0213/01/2018213/01/20183+2=5

       

      Thanks in advance.

       

      Regards,

      Sukriti