Calculating the Monthly Total at a Specific Point in Time

[TJK]

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!

[Anonymous · Accepted solution]

Hi @TJK, hello govind_021  and Poojara_D12 , thank you for your prompt reply!

Based on my understanding, I have outlined the logic below. If this is not feasible for you, please let me know!

CloseDate Range:

• The CloseDate must fall within the same month as the selected date (i.e., it should be on or after the first day of the month and on or before the last day of the month).
• The CloseDate must be later than the selected date.

CreatedDate Range:

• For each OpportunityId, only records with the maximum CreatedDate that is less than or equal to the selected date are considered.

Try the following measure:

Measure 2 = 
VAR SelectedDate = SELECTEDVALUE('Date'[Date])  
VAR StartOfMon = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1) 
VAR EndOfMonthDate = EOMONTH(SelectedDate, 0)  

RETURN
CALCULATE(
    SUM('Opportunity History'[Loan Amount]),  
    FILTER(
        'Opportunity History',
        'Opportunity History'[CloseDate] >= StartOfMon &&  
        'Opportunity History'[CloseDate] <= EndOfMonthDate &&  
        'Opportunity History'[CloseDate] >= SelectedDate   
    ),
    FILTER(
        'Opportunity History',
        'Opportunity History'[CreatedDate] = 
        CALCULATE(
            MAX('Opportunity History'[CreatedDate]),  
            ALLEXCEPT('Opportunity History', 'Opportunity History'[OpportunityId]),  
            'Opportunity History'[CreatedDate] <= SelectedDate  
        )
    )
)

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[govind_021]

Hi @TJK 
please try this code

Monthly Total Loan Amount =
VAR SelectedDate = MAX('Date'[Date]) -- The selected date from the slicer
VAR LatestRecords =
SUMMARIZE(
FILTER(
'Opportunity History',
'Opportunity History'[CreatedDate] <= SelectedDate
),
'Opportunity History'[OpportunityId],
"LatestCreatedDate",
MAX('Opportunity History'[CreatedDate])
)
VAR FilteredOpportunities =
FILTER(
ADDCOLUMNS(
LatestRecords,
"CloseDate",
CALCULATE(
MAX('Opportunity History'[CloseDate]),
'Opportunity History'[CreatedDate] = [LatestCreatedDate]
),
"LoanAmount",
CALCULATE(
MAX('Opportunity History'[Loan Amount]),
'Opportunity History'[CreatedDate] = [LatestCreatedDate]
)
),
[CloseDate] <= EOMONTH(SelectedDate, 0) -- Include only CloseDates in the selected month
)
RETURN
SUMX(FilteredOpportunities, [LoanAmount])

Regards
Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
Linkdin : www.linkedin.com/in/govind-sapkade-845104225
Youtube : http://www.youtube.com/@govind_dataanalyst

[TJK]

I am getting "Column 'LatestCreatedDate' cannot be found or may not be used in this expression."

Also what is the relationship between the date table and Opportunity History table?

[Poojara_D12]

Hi @TJK 

To calculate the total loan amount for OpportunityIds closing at a specific point in time, create a DAX measure that sums the Loan Amount for records with a CloseDate <= the selected slicer date and where the CreatedDate is also before or on the selected date. The measure should filter the data to only include the most recent record for each OpportunityId based on the latest CloseDate.

You should use the CloseDate for the relationship with the Date table. The measure will dynamically adjust based on the slicer date and consider only the latest CloseDate before the selected date, ensuring that only valid loan amounts are included.

Test the measure with various slicer dates to ensure it behaves as expected.

Loan Amount at Point in Time = 
CALCULATE(
    SUM('Opportunity History'[Loan Amount]), 
    FILTER(
        'Opportunity History', 
        'Opportunity History'[CloseDate] <= MAX('Date'[Date]) && 
        'Opportunity History'[CreatedDate] <= MAX('Date'[Date]) &&
        'Opportunity History'[OpportunityId] IN 
            CALCULATETABLE(
                VALUES('Opportunity History'[OpportunityId]), 
                FILTER(
                    'Opportunity History', 
                    'Opportunity History'[CloseDate] <= MAX('Date'[Date])
                )
            )
    )
)

[TJK]

I also tried using ChatGPT too. This code does not give an accurate output.