Forum Discussion

cfoster_atmoore's avatar
cfoster_atmoore
Regular Visitor
1 year ago
Solved

Creating a dynamic Long-Term Value Line Chart

I have a requirement to create a line chart similar to this example chart: The x-axis contains a static 5 year period that is evaluated for each year represented by a line. The y-axis measures...
  • cfoster_atmoore's avatar
    1 year ago

    I did mostly figure this out. Here is my intended end result (the only issue is I must have 5 years present in the legend in order for the measure to work properly):


    Here is my column/measure set up

    LTVYearSortOrder = 
    DATATABLE(
        "LTV Year", STRING,
        "Sort Order", INTEGER,
        {
            {"Year 1", 1},
            {"Year 2", 2},
            {"Year 3", 3},
            {"Year 4", 4},
            {"Year 5", 5}
        }
    )
    LTV Years = CALCULATE(VALUES(LTVYearSortOrder[LTV Year]), ALL(LTVYearSortOrder))
    LTV Calculation = 
    VAR Max_Year = CALCULATE(MAX('Calendar'[Year]), ALLSELECTED('Calendar'))
    VAR Year_1 = 
        CALCULATE(
            [Revenue],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year])
        )
    VAR Year_2 = 
        CALCULATE(
            [Revenue],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year]),
            'Calendar'[Year] = MIN('Calendar'[Year]) + 1
        )
    VAR Year_3 = 
        CALCULATE(
            [Revenue],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year]),
            'Calendar'[Year] = MIN('Calendar'[Year]) + 2
        )
    VAR Year_4 = 
        CALCULATE(
            [Revenue],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year]),
            'Calendar'[Year] = MIN('Calendar'[Year]) + 3
        )
    VAR Year_5 = 
        CALCULATE(
            [Revenue],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year]),
            'Calendar'[Year] = MIN('Calendar'[Year]) + 4
        )
    RETURN
    VAR Numerator = SWITCH(
                        SELECTEDVALUE(LTVYearSortOrder[LTV Year]),
                        "Year 1", Year_1 + (0 * Year_2) + (0 * Year_3) + (0 * Year_4) + (0 * Year_5),
                        "Year 2", IF(MIN('Calendar'[Year]) + 1 > Max_Year, BLANK(), Year_1 + Year_2 + (0 * Year_3) + (0 * Year_4) + (0 * Year_5)),
                        "Year 3", IF(MIN('Calendar'[Year]) + 2 > Max_Year, BLANK(), Year_1 + Year_2 + Year_3 + (0 * Year_4) + (0 * Year_5)),
                        "Year 4", IF(MIN('Calendar'[Year]) + 3 > Max_Year, BLANK(), Year_1 + Year_2 + Year_3 + Year_4 + (0 * Year_5)),
                        "Year 5", IF(MIN('Calendar'[Year]) + 4 > Max_Year, BLANK(), Year_1 + Year_2 + Year_3 + Year_4 + Year_5)
                    )
    VAR Denominator = 
        CALCULATE(
            [Active Donors],
            'Acquisition Calendar'[Year] = MIN('Calendar'[Year])
        )
    VAR LTV = DIVIDE(Numerator,Denominator,BLANK())
    RETURN
    IF(Numerator = 0, BLANK(), LTV)
    LTV = 
    IF(
        ISBLANK([LTV Calculation]),
        Blank(),
        [LTV Calculation]
    )


    Good luck and Godspeed to you all if you ever have the misfortune of having to create one of these yourself.