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

    Dynamically Set to Last Day of Each Month

    sina.d

      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

      etc..

       

      Hope it make sense.

       

      Would be grateful for any ideas.

       

      Thanks