Forum Discussion
Calculating payback period
- 8 years ago
After a fair amount of trial and error, I came up with a solution.
Step 1: Build out a helper metrics table. This serves 2 purposes: (a) excludes irrelevant metrics (like revenue), and (b) ensure costs are negative and savings are positive.
Step 2: Build 2 helper measures that will go into the virtual, summarized, intermediate table.
CumulativeTotalMetric:=CALCULATE ( SUMX ( Impact, Impact[Latest Estimate Monthly Values] * RELATED ( BaseMetrics[Payback Period Multiplier] ) ), FILTER ( ALL ( Impact[Month] ), Impact[Month] <= MAX ( Impact[Month] ) ) )TotalMetric:=SUMX ( Impact, Impact[Latest Estimate Monthly Values] * RELATED ( BaseMetrics[Payback Period Multiplier] ) )Step 3: Create the final measure that creates the virtual table (BaseTable), and performs logical operations on it to arrive at the final payback period.
Payback Period (Years):=
VAR BaseTable = ADDCOLUMNS ( SUMMARIZE ( Impact, Impact[initiative #], Impact[snapshot], Impact[Month] ), "Cumulative Total Impact", CALCULATE ( [CumulativeTotalMetric] ), "Total Impact", CALCULATE ( [TotalMetric] ) ) VAR LastCumulativeLossDate = MAXX ( FILTER ( BaseTable, [Cumulative Total Impact] < 0 ), [Month] ) VAR BreakEvenDate = MINX ( FILTER ( BaseTable, [Month] > LastCumulativeLossDate && [Cumulative Total Impact] > 0 ), [Month] ) VAR InitialInvestmentDate = MINX ( FILTER ( BaseTable, [Total Impact] < 0 ), [Month] ) RETURN IF ( OR ( ISBLANK ( InitialInvestmentDate ), ISBLANK ( BreakEvenDate ) ), BLANK (), ( BreakEvenDate - InitialInvestmentDate ) / 365 )This last meaure is pretty complicated. It uses progressive, dependent variables. It starts with the same base table, and defines variables that are used in subsequent variables. I'm no DAX expert, but I suspect using these variables helps with the calculation efficiency.
After a fair amount of trial and error, I came up with a solution.
Step 1: Build out a helper metrics table. This serves 2 purposes: (a) excludes irrelevant metrics (like revenue), and (b) ensure costs are negative and savings are positive.
Step 2: Build 2 helper measures that will go into the virtual, summarized, intermediate table.
CumulativeTotalMetric:=CALCULATE (
SUMX (
Impact,
Impact[Latest Estimate Monthly Values]
* RELATED ( BaseMetrics[Payback Period Multiplier] )
),
FILTER ( ALL ( Impact[Month] ), Impact[Month] <= MAX ( Impact[Month] ) )
)TotalMetric:=SUMX (
Impact,
Impact[Latest Estimate Monthly Values]
* RELATED ( BaseMetrics[Payback Period Multiplier] )
)Step 3: Create the final measure that creates the virtual table (BaseTable), and performs logical operations on it to arrive at the final payback period.
Payback Period (Years):=
VAR BaseTable = ADDCOLUMNS ( SUMMARIZE ( Impact, Impact[initiative #], Impact[snapshot], Impact[Month] ), "Cumulative Total Impact", CALCULATE ( [CumulativeTotalMetric] ), "Total Impact", CALCULATE ( [TotalMetric] ) ) VAR LastCumulativeLossDate = MAXX ( FILTER ( BaseTable, [Cumulative Total Impact] < 0 ), [Month] ) VAR BreakEvenDate = MINX ( FILTER ( BaseTable, [Month] > LastCumulativeLossDate && [Cumulative Total Impact] > 0 ), [Month] ) VAR InitialInvestmentDate = MINX ( FILTER ( BaseTable, [Total Impact] < 0 ), [Month] ) RETURN IF ( OR ( ISBLANK ( InitialInvestmentDate ), ISBLANK ( BreakEvenDate ) ), BLANK (), ( BreakEvenDate - InitialInvestmentDate ) / 365 )
This last meaure is pretty complicated. It uses progressive, dependent variables. It starts with the same base table, and defines variables that are used in subsequent variables. I'm no DAX expert, but I suspect using these variables helps with the calculation efficiency.