point-in-time
1 Topic
Calculating the Monthly Total at a Specific Point in Time
I'm struggling to solve this tricky "point in time" calculation. I have 4 columns in the 'Opportunity History' table. 1. OpportunityId 2. CreatedDate 3. CloseDate 4. Loan Amount I also have a 'Date' table. Each OpportunityId has multiple records, meaning the OpportunityId is not a unique identifier. Each time the CloseDate changes, a new record is created. The CreatedDate column tracks the date of change of the CloseDate, and the CloseDate column tracks the date of each new close date. The Loan Amount column has the same value for all records for each OpportunityId. I want to create a measure that equals the total loan amount (sum) that is closing in the given month at a specific point in time. For example, if I select 12/1/2024 in my slicer, I want the output to be the sum of Loan Amount of all OpportunityIds with a CloseDate equal to or before 12/31/2024 at that point in time. I want to make sure it includes all OpportunityIds with their most recent CreatedDates being before 12/1/2024, even if the most recent CreatedDate was 11/1/2024 with a CloseDate of 12/31/2024. However, if on 12/2/2024 the CloseDate changed to 1/1/2025, then I would not expect the output to include the sum of this OpportunityId when 12/2/2024 is selected in the slicer. If on 12/3/2024 the CloseDate changes back to 12/31/2024, then I would expect the output to include the sum of this OpportunityId when 12/3/2024 is selected in the slicer. I'm also not sure if the relationship between my two tables should use CreatedDate or CloseDate. All help is greatly appreciated!Solved1.2KViews2likes5Comments