Forum Discussion
Payback Period As Measure
Team,
Has anyone calculated payback period using a cumulative column calculated as a measure? The table below is sample data of 3 fields (Well, Month, and Net Cash Flow), and 1 field that is a measure (Cumulative Cash Flow).
What I would like to be able to do is calculated how many periods the Cumulative Cash Flow < 0.
I can get the data to show the days in the month by creating the PBP measure: PBP = IF([Cumulative Cash Flow] < 0, COUNTROWS(Calendar Table[Date]),0))
The issue is this measure does not total at the bottom. In fact, the total is 0 since the last value in the calculation yields 0.
Would appreciate any help here!!
| Well | Month | Net Cash Flow | Cumulative Cash Flow | PBP |
Well A | 12/31/2021 | -50000 | -50000 | 31 |
| Well A | 1/31/2022 | 10000 | -40000 | 31 |
| Well A | 2/28/2022 | 15000 | -25000 | 28 |
| Well A | 3/31/2022 | 20000 | -5000 | 31 |
| Well A | 4/30/2022 | 10000 | 5000 | 0 |
| Well A | 5/31/2022 | 20000 | 25000 | 0 |
| TOTAL |
| 0 |
4 Replies
jjstefanovic ,
Are you able to attached a sample data here?
BRAnonymous I don't have the option to attach a file on this forum but I can provide a more detailed table if that helps. See below:
Well Name Month Year Net Cash Flow - Act/FC (LOS) Cumulative Cash Flow - Forecast Well A 22-Jan 0 0 Well A 22-Feb 0 0 Well A 22-Mar 0 0 Well A 22-Apr 0 0 Well A 22-May 0 0 Well A 22-Jun 0 0 Well A 22-Jul 0 0 Well A 22-Aug 0 0 Well A 22-Sep 0 0 Well A 22-Oct 0 0 Well A 22-Nov 0 0 Well A 22-Dec 0 0 Well A 23-Jan 0 0 Well A 23-Feb 0 0 Well A 23-Mar 0 0 Well A 23-Apr 0 0 Well A 23-May 0 0 Well A 23-Jun -53122.8 -53122.8 Well A 23-Jul 0 -53122.8 Well A 23-Aug 0 -53122.8 Well A 23-Sep 0 -53122.8 Well A 23-Oct 0 -53122.8 Well A 23-Nov 0 -53122.8 Well A 23-Dec 18233.07 -34889.7 Well A 24-Jan 14426.64 -20463.1 Well A 24-Feb 12847.51 -7615.58 Well A 24-Mar 11603.66 3988.079 Well A 24-Apr 10595.84 14583.92 Well A 24-May 9760.902 24344.82 Well A 24-Jun 9056.659 33401.48 Well A 24-Jul 8453.792 41855.27 Well A 24-Aug 7931.267 49786.54 Well A 24-Sep 7473.57 57260.11 jjstefanovic
Step 1: Upload data into Power Bi (I made some changes to the date format as Power Bi was reading the month/year properly.
Step 2: Create a new MeasurePBP = CALCULATE( COUNTROWS(Sheet1), KEEPFILTERS(Sheet1[Cumulative Cash Flow - Forecast]<=0) )
Step 3: Visualize
BR , if that helps, please mark this as a solutionHi,
Based on that data that you have shared, show the expected result.
