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...
Need Help
  • cfoster_atmoore's avatar
    cfoster_atmoore
    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.

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

Hi cfoster_atmoore,

Thank you for your detailed follow-up.

Based on my understanding, kindly follow the steps outlined below, which may help resolve the issue:

  1. Keep your LTVYearSortOrder table disconnected from the main data model. Use LTVYearSortOrder[LTV Year] as the X-axis in your visual. Enable the “Show items with no data” option under visual settings to always display Year 1 through Year 5.

  2. Configure your chart as follows — X-axis: LTVYearSortOrder[LTV Year], Legend: Acquisition Gift Year and Y-axis: Updated LTV measure.

  3. Please update the LTV measure as shown below:

    LTV =
    VAR BaseYear = MIN(VWLIFECYCLE[Acquisition Gift Year])
    VAR Max_Year = CALCULATE(MAX(VWLIFECYCLE[Year]), ALLSELECTED(VWLIFECYCLE))

    VAR Year_1 = CALCULATE([Revenue], FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Year] = BaseYear))
    VAR Year_2 = CALCULATE([Revenue], FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Year] = BaseYear + 1))
    VAR Year_3 = CALCULATE([Revenue], FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Year] = BaseYear + 2))
    VAR Year_4 = CALCULATE([Revenue], FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Year] = BaseYear + 3))
    VAR Year_5 = CALCULATE([Revenue], FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Year] = BaseYear + 4))

    VAR LTV_Year = SELECTEDVALUE(LTVYearSortOrder[LTV Year])
    VAR Current_Year = YEAR(TODAY())

    VAR CumulativeRevenue =
    SWITCH(
    LTV_Year,
    "Year 1", Year_1,
    "Year 2", IF(BaseYear + 1 <= Max_Year, Year_1 + Year_2, BLANK()),
    "Year 3", IF(BaseYear + 2 <= Max_Year, Year_1 + Year_2 + Year_3, BLANK()),
    "Year 4", IF(BaseYear + 3 <= Max_Year, Year_1 + Year_2 + Year_3 + Year_4, BLANK()),
    "Year 5", IF(BaseYear + 4 <= Max_Year, Year_1 + Year_2 + Year_3 + Year_4 + Year_5, BLANK())
    )

    VAR Denominator =
    CALCULATE(
    [Active Donors],
    FILTER(ALL(VWLIFECYCLE), VWLIFECYCLE[Acquisition Gift Year] = BaseYear && VWLIFECYCLE[Lifecycle] = "New")
    )

    RETURN
    IF(
    ISBLANK(CumulativeRevenue),
    BLANK(),
    DIVIDE(CumulativeRevenue, Denominator, BLANK())
    )

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.