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.
