Calculating Payback period using DAX

[Molin]

Dear community, 

I am struggeling with at DAX measure to calculate (count) the payback period in months. Unfortunately, I have little flexibility in datamodel, meaning calculated columns is not an options.

Working on a DAX measure, I need to count the months when "Cashflow Rolling" is bigger than "Total Investment" (see picture below). Both "Cashflow Rolling" and "Total Investment" is measures, with "Total Investment" having a REMOVEFILTERS on the DimDate table [YearMonth] is related to. 

In excel I would use =MATCH(), however I cannot find a similar measure in DAX.  

Thank you in advance

[tayloramy · Accepted solution]

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. 

[Molin]

Hi @tayloramy, 

Thank you so much for your time, it worked wonders! 

Much appreciated. 

Kind regards

[v-menakakota]

Hi @Molin ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @tayloramy   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team