Capping a cumulative total

[echall93]

I'm trying to calculate a 'Realised Revenue' value by project for each month. This value should be the sum 'Cost of days' for each project, in each month, with the limitation that the total 'Realised Revenue' for a specific project cannot exceed the 'Revenue' for the project. In Excel the formula for this is:

     Realised Revenue = IF(Cumulative cost of days < Project revenue, Cost of days, IF(Cumulative last month < Revenue, Revenue - Cumulative last month,0))

I've tried to do this in two ways in Power BI, by using a measure and by created a calculated table, but I'm not succeeding with either - can anyone help?

This is my progress with measures:

Realised revenue1 = IF('Timesheets'[**bleep**. month]<MAX('Timesheets'[Revenue]),

 SUM('Timesheets'[Cost of day (optimum)]),

 IF(CALCULATE('Timesheets'[**bleep**. month],PREVIOUSMONTH('Timesheets'[Start of month].[Date]))<MAX('Timesheets'[Revenue]),

 MAX('Timesheets'[Revenue])-CALCULATE('Timesheets'[**bleep**. month],PREVIOUSMONTH('Timesheets'[Start of month].[Date])),

 0))

However this gives me the following result:

The October value for Realised revenue1 should be £15,642.74, however it seems to have subtracted the cost of day value from Septemeber, rather than the **bleep**. month value, but I'm not sure how to fix this?

Thank you in advance!

[v-yuta-msft]

@echall93 ,

Could you please share sample data and give the expected output?

Regards,

Jimmy Tao

[echall93]

Hi Jimmy Tao,

Thanks for getting back to me! This is a sample of the data I'm using for one of the projects:

Date	Project	Working days	Cost of day (optimum)	Start of month	Revenue
30 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
19 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
18 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
04 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
03 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
02 September 2019	Wilts006	1	£1,230.02	01 September 2019	51428.91
28 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
24 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
23 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
16 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
15 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
14 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
09 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
02 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
01 October 2019	Wilts006	1	£1,230.02	01 October 2019	51428.91
30 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
30 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
27 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
27 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
26 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
26 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
25 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
25 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
24 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
23 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
20 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
19 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
19 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
18 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
17 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
16 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
13 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
13 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
12 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
11 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
10 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
09 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
06 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
05 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
04 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
03 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
02 September 2019	Wilts006	1	£825.50	01 September 2019	51428.91
28 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
21 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
18 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
17 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
16 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
16 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
15 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
15 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
14 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
14 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
11 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
10 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
10 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
09 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
08 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
07 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
04 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
03 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
01 October 2019	Wilts006	1	£825.50	01 October 2019	51428.91
23 August 2019	Wilts006	1	£1,058.41	01 August 2019	51428.91
23 September 2019	Wilts006	1	£1,058.41	01 September 2019	51428.91
28 August 2019	Wilts006	1	£1,058.41	01 August 2019	51428.91
27 August 2019	Wilts006	1	£1,058.41	01 August 2019	51428.91
22 August 2019	Wilts006	1	£1,058.41	01 August 2019	51428.91
26 August 2019	Wilts006	0	£0.00	01 August 2019	51428.91
24 September 2019	Wilts006	0.5	£412.75	01 September 2019	51428.91
12 September 2019	Wilts006	0.5	£412.75	01 September 2019	51428.91
29 October 2019	Wilts006	0.5	£412.75	01 October 2019	51428.91
02 October 2019	Wilts006	0.5	£412.75	01 October 2019	51428.91
01 October 2019	Wilts006	0.5	£412.75	01 October 2019	51428.91

In the Matrix Visualisation I've then got two measures: Cumulative month and Realised revenue 1

Cumulative month = CALCULATE(
 SUM('Timesheets'[Cost of day (optimum)]),
 FILTER(
 ALLSELECTED('Timesheets'[Start of month].[Date]),
 ISONORAFTER('Timesheets'[Start of month].[Date],MAX('Timesheets'[Start of month].[Date]),DESC)
 ))

Realised revenue1 = IF('Timesheets'[Cumulative month]<MAX('Timesheets'[Revenue]),
 SUM('Timesheets'[Cost of day (optimum)]),
 IF(CALCULATE('Timesheets'[Cumulative month],PREVIOUSMONTH('Timesheets'[Start of month].[Date]))<MAX('Timesheets'[Revenue]),
 'Timesheets'[Cumulative month]-MAX('Timesheets'[Revenue]),
 0))

The expected outcome is:

Month	Monthly cost of days	Cumulative month	Realised revenue
Jul		£                -	£                -
Aug	£   4,233.64	£    4,233.64	£   4,233.64
Sep	£ 31,552.53	£  35,786.17	£ 31,552.53
Oct	£ 24,302.87	£  60,089.04	£ 15,642.74

Thank you again @v-yuta-msft!

[v-yuta-msft]

@echall93 ,

I got the same result as your first screenshot, could you please clarify the to achieve the aggregation values in Oct?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[echall93]

The value should be £15,642.74 (which is the revenue for the project, minus the realised revenue so far, i.e. £4,233.64 + £31,552.53). I'm not sure how to get Power BI to take the revenue for the project minus the cumulative revenue last month, which is what would allow me to do the above?

@v-yuta-msft 

[v-yuta-msft]

@echall93 ,

The value should be £15,642.74 (which is the revenue for the project, minus the realised revenue so far, i.e. £4,233.64 + £31,552.53). I'm not sure how to get Power BI to take the revenue for the project minus the cumulative revenue last month, which is what would allow me to do the above?

---

How to achieve £15,642.74? Could you give the calculation expression?

Regards,

Jimmy Tao