Forum Discussion

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

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.

Anonymous

Not applicable

1 year ago

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

Regular Visitor

1 year ago

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)

Forum Discussion

Creating a dynamic Long-Term Value Line Chart

Related content

strategy for org hierachies long term

Seeking Clarity on Microsoft Fabric CI/CD Long-Term Strategy

Time travel - Not recommended for long-term backup solution

Creating a dynamic slicer

Dataflows a long term solution?