Forum Discussion

echall93's avatar
echall93
Frequent Visitor
6 years ago

Capping a cumulative total

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!

Need Help

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    echall93 ,

     

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

     

    Regards,

    Jimmy Tao

    • echall93's avatar
      echall93
      Frequent Visitor
      6 years ago

      Hi Jimmy Tao,

       

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

       

      DateProjectWorking daysCost of day (optimum)Start of monthRevenue
      30 September 2019Wilts0061£1,230.0201 September 201951428.91
      19 September 2019Wilts0061£1,230.0201 September 201951428.91
      18 September 2019Wilts0061£1,230.0201 September 201951428.91
      04 September 2019Wilts0061£1,230.0201 September 201951428.91
      03 September 2019Wilts0061£1,230.0201 September 201951428.91
      02 September 2019Wilts0061£1,230.0201 September 201951428.91
      28 October 2019Wilts0061£1,230.0201 October 201951428.91
      24 October 2019Wilts0061£1,230.0201 October 201951428.91
      23 October 2019Wilts0061£1,230.0201 October 201951428.91
      16 October 2019Wilts0061£1,230.0201 October 201951428.91
      15 October 2019Wilts0061£1,230.0201 October 201951428.91
      14 October 2019Wilts0061£1,230.0201 October 201951428.91
      09 October 2019Wilts0061£1,230.0201 October 201951428.91
      02 October 2019Wilts0061£1,230.0201 October 201951428.91
      01 October 2019Wilts0061£1,230.0201 October 201951428.91
      30 September 2019Wilts0061£825.5001 September 201951428.91
      30 September 2019Wilts0061£825.5001 September 201951428.91
      27 September 2019Wilts0061£825.5001 September 201951428.91
      27 September 2019Wilts0061£825.5001 September 201951428.91
      26 September 2019Wilts0061£825.5001 September 201951428.91
      26 September 2019Wilts0061£825.5001 September 201951428.91
      25 September 2019Wilts0061£825.5001 September 201951428.91
      25 September 2019Wilts0061£825.5001 September 201951428.91
      24 September 2019Wilts0061£825.5001 September 201951428.91
      23 September 2019Wilts0061£825.5001 September 201951428.91
      20 September 2019Wilts0061£825.5001 September 201951428.91
      19 September 2019Wilts0061£825.5001 September 201951428.91
      19 September 2019Wilts0061£825.5001 September 201951428.91
      18 September 2019Wilts0061£825.5001 September 201951428.91
      17 September 2019Wilts0061£825.5001 September 201951428.91
      16 September 2019Wilts0061£825.5001 September 201951428.91
      13 September 2019Wilts0061£825.5001 September 201951428.91
      13 September 2019Wilts0061£825.5001 September 201951428.91
      12 September 2019Wilts0061£825.5001 September 201951428.91
      11 September 2019Wilts0061£825.5001 September 201951428.91
      10 September 2019Wilts0061£825.5001 September 201951428.91
      09 September 2019Wilts0061£825.5001 September 201951428.91
      06 September 2019Wilts0061£825.5001 September 201951428.91
      05 September 2019Wilts0061£825.5001 September 201951428.91
      04 September 2019Wilts0061£825.5001 September 201951428.91
      03 September 2019Wilts0061£825.5001 September 201951428.91
      02 September 2019Wilts0061£825.5001 September 201951428.91
      28 October 2019Wilts0061£825.5001 October 201951428.91
      21 October 2019Wilts0061£825.5001 October 201951428.91
      18 October 2019Wilts0061£825.5001 October 201951428.91
      17 October 2019Wilts0061£825.5001 October 201951428.91
      16 October 2019Wilts0061£825.5001 October 201951428.91
      16 October 2019Wilts0061£825.5001 October 201951428.91
      15 October 2019Wilts0061£825.5001 October 201951428.91
      15 October 2019Wilts0061£825.5001 October 201951428.91
      14 October 2019Wilts0061£825.5001 October 201951428.91
      14 October 2019Wilts0061£825.5001 October 201951428.91
      11 October 2019Wilts0061£825.5001 October 201951428.91
      10 October 2019Wilts0061£825.5001 October 201951428.91
      10 October 2019Wilts0061£825.5001 October 201951428.91
      09 October 2019Wilts0061£825.5001 October 201951428.91
      08 October 2019Wilts0061£825.5001 October 201951428.91
      07 October 2019Wilts0061£825.5001 October 201951428.91
      04 October 2019Wilts0061£825.5001 October 201951428.91
      03 October 2019Wilts0061£825.5001 October 201951428.91
      01 October 2019Wilts0061£825.5001 October 201951428.91
      23 August 2019Wilts0061£1,058.4101 August 201951428.91
      23 September 2019Wilts0061£1,058.4101 September 201951428.91
      28 August 2019Wilts0061£1,058.4101 August 201951428.91
      27 August 2019Wilts0061£1,058.4101 August 201951428.91
      22 August 2019Wilts0061£1,058.4101 August 201951428.91
      26 August 2019Wilts0060£0.0001 August 201951428.91
      24 September 2019Wilts0060.5£412.7501 September 201951428.91
      12 September 2019Wilts0060.5£412.7501 September 201951428.91
      29 October 2019Wilts0060.5£412.7501 October 201951428.91
      02 October 2019Wilts0060.5£412.7501 October 201951428.91
      01 October 2019Wilts0060.5£412.7501 October 201951428.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:
      MonthMonthly cost of daysCumulative monthRealised 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's avatar
        v-yuta-msft
        Community Support
        6 years ago

        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.