Forum Discussion
cfoster_atmoore
1 year agoRegular Visitor
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...
- 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 upLTVYearSortOrder = 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.
cfoster_atmoore
1 year agoRegular Visitor
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.