Repeat Value every month until Specific Date

Hi MJ153614 ,

The issue arises because the measure is aggregating charges across all available months instead of displaying the appropriate monthly charge. Since the data is appended each month, the solution should ensure that each month reflects the correct charge for that period while carrying forward the most recent charge when no new charge is available. To achieve this, the measure retrieves the latest charge record for the current month and prevents cumulative aggregation across months. It first determines the current date from the Dates table, then identifies the membership start and end dates to ensure that charges are only displayed within the valid membership period. The measure looks for the most recent charge record on or before the current date and returns only that value, ensuring that for example, a sequence of 500, 306.15, 250, and 250 appears correctly rather than summing all charges into a single total.

Monthly Effective Charge =
VAR CurrentDate = MAX(DatesTable[Date])
VAR MemberID = MAX(MembersTable[MemberID])
VAR MembershipStart =
    CALCULATE(
        MIN(MembersTable[StartDate]),
        ALLEXCEPT(MembersTable, MembersTable[MemberID])
    )
VAR MembershipEnd =
    CALCULATE(
        MAX(MembersTable[EndDate]),
        ALLEXCEPT(MembersTable, MembersTable[MemberID])
    )
VAR IsWithinMembership = CurrentDate >= MembershipStart && CurrentDate <= MembershipEnd

VAR LastUpdateDate =
    CALCULATE(
        MAX(ChargesTable[ChargeDate]),
        FILTER(
            ALL(ChargesTable),
            ChargesTable[ChargeDate] <= CurrentDate &&
            ChargesTable[MemberID] = MemberID
        )
    )

VAR EffectiveCharge =
    CALCULATE(
        SUM(ChargesTable[ChargeAmount]),
        FILTER(
            ALL(ChargesTable),
            ChargesTable[ChargeDate] = LastUpdateDate &&
            ChargesTable[MemberID] = MemberID
        )
    )

RETURN
IF(IsWithinMembership, EffectiveCharge, BLANK())

This measure ensures that the latest charge for each month is displayed correctly, rather than summing all charges indefinitely. If a new charge is recorded, it updates accordingly, but if no charge is recorded for a given month, the previous charge is carried forward until a new one is found or the membership period ends. If the chart still does not display the expected results, checking that the Dates table is correctly marked as a date table and ensuring that no unexpected filters interfere with the calculations would be necessary. Let me know if further refinements are needed.

Best regards,