Repeat Value every month until Specific Date

[MJ153614]

Good day, 

I am having difficulty creating a formula to calculate the total charges for a member each month and repeat this value until the end of their membership. Eventually, this will be used in a visualization for a burn-down chart.

I was trying to do something like the image below.

TIA 🙂

[DataNinja777]

Hi @MJ153614 ,

To calculate the total charges for a member each month and repeat this value until the end of their membership, we can use a DAX measure that ensures the charges are displayed dynamically across the membership period. This measure will sum the total charges for each member and persist that value until the membership ends.

Total Charges Until End = 
VAR StartDate = MIN(MembersTable[StartDate])
VAR EndDate = MAX(MembersTable[EndDate])

RETURN 
    IF(
        MAX(DatesTable[Date]) >= StartDate && MAX(DatesTable[Date]) <= EndDate,
        CALCULATE(
            SUM(ChargesTable[ChargeAmount]),
            ALLEXCEPT(MembersTable, MembersTable[MemberID])
        ),
        BLANK()
    )

This measure first determines the start and end dates of the membership for each member in context. It then checks whether the current date falls within this period, ensuring that the charge value repeats every month until the membership ends. The CALCULATE function aggregates the total charges while maintaining filtering by MemberID using ALLEXCEPT, preventing unintended aggregations. If the date is within the membership range, the total charge is displayed; otherwise, it remains blank. This approach enables a clear visualization of membership charges over time, making it suitable for a burn-down chart.

Best regards,

[MJ153614]

Thank you for your prompt response. I attempted the solution, and while it worked partially, I have some concerns. The measure currently calculates the entire table, which causes the total to repeat until the member's end date. The data is pulled on a monthly basis and appended to the previous month's data. I need the measures to reflect the actual charges for each member on a monthly basis. Additionally, if the member's end date has not yet arrived, I want the current charges from the most recently pulled data to continue being reflected until the member's end date.

We can obtain the date range from the first data pulled to the last expected end date for the members. However, the monthly charges are not correct. For example, the 2nd row in the table below should be spread into 
500,306.15,250,250 respectively instead of 1,306.15

Btw, I tried using the measure in a chart and it seems like it is not working, I might be doing something wrong. Please let me know 

TIA 🙂

[DataNinja777 · Accepted solution]

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,

[MJ153614]

Thank you for your help with this; it works well with the data table. However, when I try to use it in a bar chart, the charges are aggregated strangely, and it stops calculating charges for future months. I might be doing something incorrectly, but I will investigate further. I appreciate your support in working with me.

[Syndicate_Admin]

Nice day
There is a function called DATESBETWEEN, which will generate a line of data for each day between the start date and the end date, this is an example
-DATESBETWEEN('Date Table 2'[Date],Table[Start Date],Table[End Date]))

I hope I have helped you

Best regards