Create custom column to create cumulative histogram

[Anonymous]

I am looking for support in carrying out the following:

1. There is a table with a column that has Revenue plan numbers and details like country, service, etc. BUT no date

2. I would like to create a custom column that will help me calculate the revenue plan numbers in each row in a way that I have the cumulative numbers for each month. For example: If the total revenue plan is 10,000. January will be 10000/12, February will be (10,000/12)*2, March will be ((10000/12)*2)+ (10000/12) and so on until by December it adds up to the Total revenue plan amount of 10,000.

Unable to figure out a way to do this. Some help will be really appreciated.

[v-piga-msft]

Hi @Anonymous ,

To be more helpful, please share some data sample which could reproduce your scenario and your desired output so that we could copy and test on it.

Best  Regards,

Cherry

[Anonymous]

Hi @v-piga-msft ,

I have created a sample data and visualization for you in excel. The formulas that I used to calculate the cumulative in excel are:

Jan = Total revenue plan / 12 i.e. 280,000/12 = 233,333

Feb = 233,333*2

Mar = 466,667 + 233,333

Apr = 700,000 + 233,333 , and so on

I need to create the same thing on Power BI. Hope the sample helps.

Data sampleVisualization sample

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your requirement, you could create a calendar table firstly.

Calendar = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

Then create the measure with SWITCH function to calculate the revenue for each month. You could refer to my expression like below. [SUM ] is also a measure.

SUM = CALCULATE(SUM('Table'[Revenue]))

reve =
VAR a = [SUM] / 12
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Calendar'[Date].[Month] ) = "January", a,
        MAX ( 'Calendar'[Date].[Month] ) = "February", a * 2,
        MAX ( 'Calendar'[Date].[Month] ) = "March", a * 2 + a,
        ....,
        MAX ( 'Calendar'[Date].[Month] ) = "December", [your expression],
        Blank()
    )

Here is my test example, I only calculate for three month.

Hope this can help you.

Best  Regards,

Cherry