User Profile
cfoster_atmoore
Regular Visitor
Joined 1 year ago
User Widgets
Contributions
Re: Creating a dynamic Long-Term Value Line Chart
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.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 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.
