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.
v-pnaroju-msft
1 year agoCommunity Support
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.