3 Replies Latest reply on Feb 6, 2019 8:29 AM by Shinichiro Murakami

    Using Relative Dates not in Data Source for Calculations

    Mike Martin

      I apologize if this has been discussed, but I've done a lot of searching and haven't found anything. I think what I'm wanting to do may not be possible, but I wanted to check anyway.

       

      I have a table of opportunities from a CRM platform that I'm reporting from. Opportunities are fluid and the records change from week to week. All I really need to do is track the # of open opportunities based on relative periods (e.g. the last 4 months). In this scenario, it would look at how many opportunities were still open at the end of that month. I want to show a continuous graph by salesperson for each of the last 4 periods.

       

      The data has the opportunity, the date it was created, the salesperson, if it's closed, and the date it was closed. If I have a given date, I can calculate which ones were open then based on this data. But I want the column to always be the previous 4 months (or weeks). If it's still open, it would be included if the created date was before the end of the month/week. If it's closed, it would be included if the closed date was after the end of the month/week and the created date.

       

      I've attached a sample workbook with dummy data.

       

      Any suggestions?