Create Measure to calculate cumulative total of revenue based on reducing balance.

[Anonymous]

Hi There,

I'm new to forum and have started using Powerbi recently.

I have a dataset which gives me revenue for each client and product on a daily basis.

I need to create a measure which gives me total revenue for the month and then calculates expected revenue upto a certain month.
In below example I want to calculate expected revenue by Dec 2020 that was received in Jan 2020.
Expected Revenue would need to consider reduction in revenue by 1% on a montly basis.

i.e Revenue in Jan is £100 , expected revenue in Feb will reduce by 1% to £99, in March it will reduce by further 1% £98.01 and so... till Dec where revenue would be £89.53. Giving a total Revenue of £1,136.15

	Jan	Feb	Mar	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
Client Product	£100	£99	£98.01	£97.03	£96.06	£95.10	£94.15	£93.21	£92.27	£91.35	£90.44	£89.53	£1,136.15

So final Output should be

	Jan	Measure Calculation
Client Product	£100	£1,136.15

Any help would be appreciated. I have now attached my PBIX .

Thanks in advance.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created some sample data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column and a measure as below.

Calculated column:
YearMonth = VALUE(FORMAT('Table'[Date],"yyyymm"))

Measure:
Result = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Category],
    'Table'[YearMonth],
    "new",
        var _baserevenue = 
            CALCULATE(
                SUM('Table'[Revenue]),
                FILTER(
                    ALL('Table'),
                    'Table'[Category] = EARLIER('Table'[Category])
                )
            )
        var _minyearmonth = 
        CALCULATE(
            MIN('Table'[YearMonth]),
            FILTER(
                ALL('Table'),
                'Table'[Category] = EARLIER('Table'[Category])
            )
        )
        var _num = 'Table'[YearMonth]-_minyearmonth
        return POWER(0.99,_num)*_baserevenue
)
return
SUMX(
    tab,
    [new]
)

Result:

Best Regards

Allan

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

[Anonymous]

Thank you @v-alq-msft ,

Is it possible to use the sample file as I had supplied ? as the sample file created by yourself is a bit different to mine.
I do not have future date rows inserted in mine.

Appreciate your time for looking into this.

[mahoneypat · Accepted solution]

@Anonymous 

I downloaded your pbix and came up with the two measures below that return your expected result.  Just put the 2nd one in your visual.

Adj Running Total =
VAR monthsaway =
DATEDIFF ( MAX ( Scorecard[Calldate] ), DATE ( 2020, 12, 31 ), MONTH )
VAR thismonthsum =
SUM ( Scorecard[Revenue] )
VAR numbers =
GENERATESERIES ( 0, monthsaway )
VAR adjtotal =
SUMX ( numbers, thismonthsum * POWER ( 0.99, [Value] ) )
RETURN
adjtotal

Final Measure =
SUMX (
SUMMARIZE ( Scorecard, Scorecard[Calldate].[Month], Scorecard[ProgramID] ),
[Adj Running Total]
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

This works for me Thank you @mahoneypat  !

[Ashish_Mathur]

Hi,

This would depend upon the structure of your data.  Share the link from where i can download your PBI file.

[Anonymous]

Hi Ashish.

Thank you for getting back. Apppreciate your time looking into this. Let me know if there are any issues accessing the file.

if you have email address I can email you directly.

Here is my PBIX.

[Ashish_Mathur]

Hi,

I have been able to solve it by using measures only (without creating a column for each month).  Here is the screenshot comparing your result with mine.  My solution is on the left hand side table and yours is on the right.  I think there is a mistake in your solution.  The figure in the highlighted cell should be 268,708.57 (as shown in the table on the left).

Please confirm.

[Anonymous]

Thank you for getting back @Ashish_Mathur .

You are correct some copy paste formula had gone wrong in my desired result and your calcuation is having appropriate values.

It seems I have got the result I wanted. Would you mind sharing your Pbix please.

[ryan_mayu]

@Anonymous 

I am not sure if I can get the correct answer for this.

At first, I create a test table. I set the latest month data was Jan and amount was 100

I create a measure to calculate the latest month's amount

latestmonthamount = 
VAR maxdate =max('dataset'[date])
Return CALCULATE(sum('dataset'[amount]),FILTER('dataset',month('dataset'[date])=month(maxdate)))

Then I create a measure to calculate the reducing balance

Measure = 
VAR V2 = [latestmonthamount]*0.99
VAR V3 = [latestmonthamount]*0.99^2
VAR V4 = [latestmonthamount]*0.99^3
VAR V5 = [latestmonthamount]*0.99^4
VAR V6 = [latestmonthamount]*0.99^5
VAR V7 = [latestmonthamount]*0.99^6
VAR V8 = [latestmonthamount]*0.99^7
VAR V9 = [latestmonthamount]*0.99^8
VAR V10 = [latestmonthamount]*0.99^9
VAR V11= [latestmonthamount]*0.99^10
VAR V12= [latestmonthamount]*0.99^11
VAR A1 = [latestmonthamount]+V2+V3+V4+V5+V6+V7+V8+V9+V10+V11+V12
VAR A2 = [latestmonthamount]+V2+V3+V4+V5+V6+V7+V8+V9+V10+V11
VAR A3 = [latestmonthamount]+V2+V3+V4+V5+V6+V7+V8+V9+V10
VAR A4 = [latestmonthamount]+V2+V3+V4+V5+V6+V7+V8+V9
VAR A5 = [latestmonthamount]+V2+V3+V4+V5+V6+V7+V8
VAR A6 = [latestmonthamount]+V2+V3+V4+V5+V6+V7
VAR A7 = [latestmonthamount]+V2+V3+V4+V5+V6
VAR A8 = [latestmonthamount]+V2+V3+V4+V5
VAR A9 = [latestmonthamount]+V2+V3+V4
VAR A10= [latestmonthamount]+V2+V3 
VAR A11 = [latestmonthamount]+V2
VAR month = month(MAX('dataset'[date]))
Return switch(TRUE(),month=1,A1,month=2,A2,month=3,A3,month=4,A4,month=5,A5,month=6,A6,month=7,A7,month=8,A8,month=9,A9,month=10,A10,month=11,A11,[latestmonthamount])

Because I don't if the Jan is your suggestion or the real month you want to calculate. So I list all the possibilities. Jan to Dec, Feb to Dec until Nov to Dec based on the latest month.

You can modify the DAX coding. Hope this is helpful.

[Anonymous]

Thanks Ryan for getting back.
Your solution is not fesiable only because the revenue 12 months revenue calculation migh be stretched to 5 years. So i need more dynamic calcuations in place rather than making variable for each month.

I have now atatched my powerbi file PBIX  , incase you need to have a look.

Appreciate your help so far.

[ryan_mayu]

@Anonymous 

I used the similar method can get the result. I am not sure if we can make this easier. Let's see if anyone else can provide better solution