Calculating payback period using DAX

1
votes

I'm working on some calculations for capital budgeting, and I have the following two tables in my data model

Impact Table Project Table

I'm trying to build out a calculated column in DAX to determine the payback period for each project in the Project table. I've put together the calculation here, I'm just not sure exactly how to execute this in DAX.

Logical Steps for Calculating Payback Period:

  1. For each Project, find the cumulative sum for each date for relevant metrics (Include OpEx Savings and OpEx Implementation Cost, but not Revenue or Working Capital)

  2. Find the MIN date where cumulative sum is greater than zero (the "break-even" date")

  3. Find the MIN date with non-zero implementation cost ("Investment date")

  4. Find the difference (in months) between #2 and #3 to determine payback period

EDIT:

The answer for the listed project is 7 months. I've built an intermediate table in Excel to develop the answer, but I'd like to be able to do this directly in a PowerPivot table with DAX.

Answer

2
daxpowerpivot
Could you give the expected outcome of your 4 figures, based on your sample? It looks like this (2) = 1/1/2018 (because OpEx Implementation > 0) and (3) = 1/1/2018 (because OpEx Implementation <> 0). I'm guessing this ain't right? – TJ_
I've included the expected outcome above. Note that revenue and working capital metrics are excluded from this calc. – Austin Wismer

2 Answers

0
votes

I've produced this as a solution:

  1. Create values, which makes sure cost are - and savings are + (ValCorr)
  2. Create a running sum (RunningSum)
  3. Find Investment Date (InvestmentDate)
  4. Find Breakeven Date (BreakEvenDate)
  5. Find Difference (Payback)

DAX: 

RunningSum = 
CALCULATE(SUM(Impacts[ValCorr]);
    FILTER(
        ALL(Impacts);
        Impacts[ProjectID] = EARLIER(Impacts[ProjectID]) &&
        Impacts[Date] <= EARLIER(Impacts[Date])
    ))


InvestmentDate =
CALCULATE (
    FIRSTNONBLANK ( Impacts[Date]; 0 );
    FILTER ( ALL ( Impacts ); Impacts[RunningSum] <> 0 )
)

BreakEvenDate =
CALCULATE (
    FIRSTNONBLANK ( Impacts[Date]; 0 );
    FILTER ( ALL ( Impacts ); Impacts[RunningSum] > 0 )
)

Payback = DATEDIFF(Impacts[InvestmentDate];Impacts[BreakEvenDate];MONTH)

Result: Result

Good luck!

0
votes

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.

Metrics Table

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.

EDIT: I should note that I didn't use this measure as a calculated column -- I simply used it in a pivot table which is the same "shape" as the "Projects" table above -- one line per project / initiative.