Calculating Payback period using DAX

Hi Molin, 

What you are running into is expected: DAX measures do not have an Excel-style MATCH that scans an array. Measures evaluate in the current filter context, so to compute a payback period you need to scan the month set yourself and find the first month where your cumulative cash flow meets or exceeds the (context-constant) Total Investment. We can do that with CALCULATE + FILTER over the date axis (docs: CALCULATE, ALLSELECTED).

Try this: 

Payback Months :=
VAR Invest = [Total Investment]             -- expected to ignore DimDate via REMOVEFILTERS
VAR FirstPaybackDate =
    CALCULATE(
        MIN ( 'DimDate'[Date] ),            -- the first date where rolling >= invest
        FILTER(
            ALLSELECTED ( 'DimDate'[Date] ),-- scan the visible date range (respects slicers)
            [Cashflow Rolling] >= Invest
        )
    )
VAR StartDate =
    CALCULATE( MIN ( 'DimDate'[Date] ), ALLSELECTED ( 'DimDate'[Date] ) )
RETURN
IF (
    ISBLANK ( FirstPaybackDate ),
    BLANK(),                                -- no payback in the selected window
    DATEDIFF ( StartDate, FirstPaybackDate, MONTH ) + 1
)

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.