3 Replies Latest reply on Jan 23, 2017 6:44 AM by Norbert Maijoor

    Dynamically Set to Last Day of Each Month


      Hi All,


      I am currently using this calculation to get the average number of days between the Open Date to date.

      AVG(DATEDIFF('day', [Open Date], TODAY()))


      This shows an aggregation of all months to date. However instead I need to dynamically compare [Open Date] to the last day of each month. I should then get a monthly breakdown in my result e.g:


      As at (Month/Year)      | Avg number of days

      January 2017                 | 25

      February 2017               | 39

      Mars 2017                      | 31

      April 2017                       | 15

      May 2017                       | 12



      Hope it make sense.


      Would be grateful for any ideas.