Can Power BI Calculate Accumulating Amounts with Different Start Dates Over Time?

[Oaabwen42]

The subject line might make this sound more complex than this really is. I want to make a column chart visualization that shows something like the following example:

There is a monthly fee of $250, inclusive of the start month.

Client 1 onboards in April.

Client 2 onboards in June.

Client 3 onboards in September.

What is the total revenue for each month?

Jan: $0 | Feb: $0 | Mar: $0 | Apr: $250 | May: $250 | June: $500 | July: $500 | August: $500 | September: $750...

So in the chart each month is a column and the y value is the total revenue.

My real data is more complex than this, but this is what I need at its simplest form. Is there any way to calculate this?

[Ashish_Mathur]

Hi,

Study my solution in the 2 attached files and adapt it to your data.

Hope this helps.

[Oaabwen42]

Thank you! I'm reviewing the 'Split revenue over years' report and you've mostly done the same thing I'm trying to do, but over many years instead of one. I created a column chart based on the data in that report, and the ongoing revenue does indeed accumulate.

However, I've recreated your measures with my own data and I can't get the same effect. If a 'subscription' starts in July, there should be revenue for July and August and September and so on. This is what you seem to get, but in my report I get this:

It's still lumping the whole ongoing into the start month instead of spreading it out. I'm not sure what I did wrong here.

[Ashish_Mathur]

Study those examples carefully.  Keep trying.

[Oaabwen42]

I'm following the examples with fake data and encountering the same problem. PBI lumps the whole amount into the start month instead of distributing it over all relevant months.

I've copy and pasted the DAX expressions you have in your example (Split revenue over years, revenue apportioning is not what I'm trying to do) am trying to understand why the difference occurs. Some questions and points:

- What is the purpose of the "Days" column? I don't see it used

- Project duration and project duration within the year, for my data, will always be 365 when counting by day and 12 when counting by month

- Because the 2 project durations are the same value, your measures are really just dividing revenue by project duration to get revenue per day, then multiplying revenue per day by project duration. So the measure is equal to revenue. Nothing has changed. What in the expression tells PBI to distribute over each relevant month/year?

- Changing project duration within the year to project duration in Measure changes the result:

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Ashish_Mathur]

Hi,

Study my solution in the 2 attached files and adapt it to your data.

Hope this helps.

[vicky_ · Accepted solution]

I think you're looking for running totals - check out this article on SQLBI which goes through the pattern for such a calculation: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

[Oaabwen42]

Like the other solution, this seems like what I need but when I use it with my own data PBI returns nonsense:

[Oaabwen42]

Doing the same thing with test data returns what I want. My actual report was just being buggy.