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 are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.
If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.
Thank you.