Forum Discussion

Lightsong90's avatar
Lightsong90
Frequent Visitor
1 year ago
Solved

DAX Optimization Help Needed - Measure That Calculates On Each Date Point in a Line Chart

Hi all, I have a measure that calculates retention, and my goal is to plot retention over time. The measure works as intended, but when I plot it across more than a few date points, the chart loads ...
  • johnt75's avatar
    1 year ago

    You could try creating a summary table which pre-calculates the rate for a given day.

    Dynamic Retention Rate Table =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
        "Retention rate",
            VAR SelectedDate = [Date]
            VAR SelectedDateMinus367 = SelectedDate - 367
            VAR CurrentMembers =
                CALCULATETABLE (
                    DISTINCT ( 'Invoice'[membership__c] ),
                    ALL ( 'Invoice' ),
                    'Invoice'[CreatedDate] <= SelectedDate,
                    'Invoice'[Expiration_Date__c] >= SelectedDate,
                    'Invoice'[Membership_Type__c] <> "Trial Membership"
                )
            VAR Members367DaysAgo =
                CALCULATETABLE (
                    DISTINCT ( 'Invoice'[membership__c] ),
                    'Invoice'[CreatedDate] <= SelectedDateMinus367,
                    'Invoice'[Expiration_Date__c] >= SelectedDateMinus367
                )
            VAR ActiveBoth =
                INTERSECT ( CurrentMembers, Members367DaysAgo )
            VAR CountActiveBoth =
                COUNTROWS ( ActiveBoth )
            VAR CountMembers367DaysAgo =
                COUNTROWS ( Members367DaysAgo )
            RETURN
                DIVIDE ( CountActiveBoth, CountMembers367DaysAgo )
    )
    

    It would take a while to create the table during data refresh, but that would be invisible to end users.