Creating a dynamic Long-Term Value Line Chart

[cfoster_atmoore]

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 LTV. As an example, Year 5 LTV = Cumulative Total Revenue in Year 1 + Year 2 + Year 3 + Year 4 + Year 5 from donors acquired in Year 1 divided by the total donors acquired in year 1.

I have created a helper table for the LTV years:

LTVYearSortOrder = 
DATATABLE(
    "LTV Year", STRING,
    "Sort Order", INTEGER,
    {
        {"Year 1", 1},
        {"Year 2", 2},
        {"Year 3", 3},
        {"Year 4", 4},
        {"Year 5", 5}
    }
)

A calculated column to tie it to my dataset:

LTVYearAssigner = 
VAR BaseYear = VWLIFECYCLE[Acquisition Gift Year]
RETURN 
SWITCH(
    TRUE(),
    VWLIFECYCLE[Year] = BaseYear, "Year 1",
    VWLIFECYCLE[Year] = BaseYear + 1, "Year 2",
    VWLIFECYCLE[Year] = BaseYear + 2, "Year 3",
    VWLIFECYCLE[Year] = BaseYear + 3, "Year 4",
    VWLIFECYCLE[Year] = BaseYear + 4, "Year 5",
    BLANK()
)

and a measure to calculate LTV:

LTV = 
VAR Year_1 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Lifecycle] = "New"
            )
    )
VAR Year_2 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+1
            )
    )
VAR Year_3 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+2
            )
    )
VAR Year_4 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+3
            )
    )
VAR Year_5 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[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",Year_1 + Year_2 + 0*Year_3 + 0*Year_4 + 0*Year_5,
                    "Year 3",Year_1 + Year_2 + Year_3 + 0*Year_4 + 0*Year_5,
                    "Year 4",Year_1 + Year_2 + Year_3 + Year_4 + 0*Year_5,
                    "Year 5",Year_1 + Year_2 + Year_3 + Year_4 + Year_5
                )
VAR Denominator = 
    CALCULATE(
            [Available Donors],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Lifecycle] = "New"
            )
    )
VAR LTV = DIVIDE(Numerator,Denominator,0)
RETURN
LTV

There are two things wrong with my measure:
1. If I don't have multiple years selected in the Year slicer, it turns into a flat line, I assume this is due to my numerator calculation. I need it to work with a dynamic number of years selected.

2. If a year's LTV year is incomplete or in the future, the line should not extend to that year. Again, I think this is due to my numerator. I need a way to say if [LTVYearAssigner] =Blank() then Blank(). For example, 2024 should only have a value for 2024 (a dot basically) and should be blank for years 2-5 

Thank you in advance for your  help.

[cfoster_atmoore · Accepted solution]

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]

Hi cfoster_atmoore,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi cfoster_atmoore,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi cfoster_atmoore,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

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.

[Anonymous]

Hi, @cfoster_atmoore 

Please try the following DAX formula:

LTV = 
VAR SelectedYears = VALUES(VWLIFECYCLE[Year])
VAR BaseYear = MIN(VWLIFECYCLE[Acquisition Gift Year])
VAR MaxYear = MAX(VWLIFECYCLE[Year])
VAR CurrentYear = YEAR(TODAY())

VAR CumulativeRevenue = 
    SUMX(
        FILTER(
            ALLSELECTED(VWLIFECYCLE),
            VWLIFECYCLE[Acquisition Gift Year] = BaseYear &&
            VWLIFECYCLE[Year] <= MaxYear &&
            VWLIFECYCLE[Year] <= CurrentYear
        ),
        [Revenue]
    )

VAR TotalDonors = 
    CALCULATE(
        [Available Donors],
        FILTER(
            ALLSELECTED(VWLIFECYCLE),
            VWLIFECYCLE[Acquisition Gift Year] = BaseYear &&
            VWLIFECYCLE[Lifecycle] = "New"
        )
    )

VAR LTV = DIVIDE(CumulativeRevenue, TotalDonors, 0)

RETURN
IF(
    MaxYear <= CurrentYear,
    LTV,
    BLANK()
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cfoster_atmoore]

Hi @Anonymous , thank you for your response. I was able to user your measure as inspiration for some changes on my original measure and I was able to fix the second issue I listed. The thing I'm still stuck on now is how to make the x-axis constantly show all 5 [LTV year]s even if there is only 1 [year] selected in the slicer.

Here is the updated measure:

LTV = 
VAR Max_Year = CALCULATE(MAX(VWLIFECYCLE[Year]), ALLSELECTED(VWLIFECYCLE))
VAR Year_1 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Lifecycle] = "New"
            )
    )
VAR Year_2 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+1
            )
    )
VAR Year_3 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+2
            )
    )
VAR Year_4 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[Year])+3
            )
    )
VAR Year_5 = 
    CALCULATE(
            [Revenue],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Year] = MIN(VWLIFECYCLE[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(VWLIFECYCLE[Year])+1 > Max_Year,0,Year_1 + Year_2 + 0*Year_3 + 0*Year_4 + 0*Year_5),
                    "Year 3",IF(MIN(VWLIFECYCLE[Year])+2 > Max_Year,0,Year_1 + Year_2 + Year_3 + 0*Year_4 + 0*Year_5),
                    "Year 4",IF(MIN(VWLIFECYCLE[Year])+3 > Max_Year,0,Year_1 + Year_2 + Year_3 + Year_4 + 0*Year_5),
                    "Year 5",IF(MIN(VWLIFECYCLE[Year])+4 > Max_Year,0,Year_1 + Year_2 + Year_3 + Year_4 + Year_5)
                )
VAR Denominator = 
    CALCULATE(
            [Active Donors],
            FILTER(
                ALLSELECTED(VWLIFECYCLE),
                VWLIFECYCLE[Acquisition Gift Year] = MIN(VWLIFECYCLE[Year]) && VWLIFECYCLE[Lifecycle] = "New"
            )
    )
VAR LTV = DIVIDE(Numerator,Denominator,BLANK())
RETURN
IF(Numerator = 0, BLANK(), LTV)