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 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.