Calculating Project Payback Period using DAX


Hi, I am new to this forum and hoping to get some assistance regarding the calculation of Project Payback Period (in Years ##.#). I have used the following DAX measures to visualise the intersection between cumulative project cost savings and cumulative project cost (expenditure), however I would like to take this a step further by providing a numeric visual (tile) of the payback period in years, as we will be doing some what if analysis with other stakeholders.

Measure 1:

Cumulative Cost Savings AUD:=CALCULATE([Total Cost Savings AUD],FILTER(ALL(DIM_Date),DIM_Date[Date]<=MAX(DIM_Date[Date])))

Measure 2:

Cumulative Project Cost AUD:=CALCULATE([Total Project Cost AUD],FILTER(ALL(DIM_Date),DIM_Date[Date]<=MAX(DIM_Date[Date])))

Measure 3:

MIN Project Cost Date:=MINX(FACT_ProjectCost,FACT_ProjectCost[Date])

I assume that the next steps would be to use DAX formula(s) to identify the earliest date where the cumulative cost savings are equal to the SUM total of the project cost and then deducting the earliest date in which an expense occurred on the project cost table (measure 3 above) to get the number of days between the two dates and dividing by 365; however I am not sure where to start with this.

Any help would be sincerely appreciated.


Welcome to the forum. Be a little more specific. What measure(s) are you trying to come up with? Calculating between 2 dates you could use the DATEDIFF function in DAX, and then you could use that result in combination with the DIVIDE function to get your result. It also helps if you share a PBIX file that give us a better picture of what we are looking at.