Annual trend of Monthly recurring revenue

[Lodan]

Hello,

I have only started to use Power BI recently and have been having a lot of fun with it.  However I have hit a snag with one of the visuals I have been trying to create and looking for some guidance please.

I am trying to re-create in power BI something that an ex colleague had done in excel.  It takes input from a spreadsheet and calculates and outputs the total MRR on a monthly basis that is then also rolled up to a quarterly and yearly basis.  See below:

The source data looks like this:

I did have some success in my research and found an example where someone had done something similiar for a HR purpose of tracking number of people.  So using that as an example I created a dates table and added two relationships.  What was a bit odd was that when one of those was active I got some strange results but with both inactive it looks better.

I then created the following measure:

MRR Revenue = CALCULATE(SUMX(filter(TblCombined,TblCombined[Calculated End Date]>max('Dates2'[Date])

    && TblCombined[(estimated) (billing) start date]<=max('Dates2'[Date])

    && or(TblCombined[PON/Other]="PON",TblCombined[PON/Other]="Excluded")),

    TblCombined[MRR ($)]))

Now if I am correct what that is doing is filtering based on the end date being greater than the maximum date of the context chosen and a start date before the same date.  So if I choose months then for july it would pick 31st July.  I am also filtering on a couple other columns and then adding up the revenue from the outcome.

This then gives me this:

In the top table it looks about right although the total row is a bit weird but I wasn't planning to use that.

However when I try to do the yearly view it goes wrong.  As not expected but makes sense its providing the same logic and picking the maximum date of the context which would be 31st december and performing the sum based on that with the filtering.

What I actually want it to do is perform it for each month of the year and then add them together to get the total for the year if that makes sense.

I couldn't work out how to set the context for the measure to be monthly and then sum up all the month results for the given year.

Thanks very much in advance for any help or guidance.  My google searches weren't getting me anywhere.

[Lodan]

That is outstanding, thanks very much!

[johnt75 · Accepted solution]

The general pattern for getting the total to be calculated as the sum of the rows is to do a SUMX over the VALUES of the column you are using in the rows of the matrix.

MRR Revenue =
SUMX (
    VALUES ( 'Dates2'[Month Year] ),
    CALCULATE (
        SUM ( TblCombined[MRR ($)] ),
        TblCombined[Calculated End Date] > MAX ( 'Dates2'[Date] ),
        TblCombined[(estimated) (billing) start date] <= MAX ( 'Dates2'[Date] ),
        TblCombined[PON/Other] IN { "PON", "Excluded" }
    )
)

I tweaked your code to conform to some best practices. Never use FILTER on an entire table, just filter the columns that you need to. You can get this behaviour by passing filter conditions directly into CALCULATE without using the FILTER function.

[Lodan]

Not sure if follow ups were allowed or if it should be a new post but thought I would try.  

I tried to do the same thing with NRR:

NRR Revenue = 
SUMX (
    VALUES ( 'Dates2'[Month Year] ),
    CALCULATE (
        SUM ( TblCombined[NRR ($)] ),
        TblCombined[Calculated End Date] >= MAX ( 'Dates2'[Date] ),
        TblCombined[(estimated) (billing) start date] <= MAX ( 'Dates2'[Date] ),
        TblCombined[PON/Other] IN { "PON", "Excluded" }
    )
)

---

---

---

---

 And then did a Total Revenue Measure:

Total Revenue = [MRR Revenue]+[NRR Revenue]

What I got was good but also a bit strange:

MRR looks about right although the MRR for the year is less than the MRR for the total of the 12 months of the year.

The inidividual NRR values lok about right but the total isn't correct and on the year table its both wrong and different to the total of the month column.

Any idea on what could be causing this please?

Thans

[johnt75]

I think that the relationship between date and the billing start date might be causing problems. As the measures already factor in the appropriate logic for that try making the relationship inactive.

[Lodan]

Unfortunately they already were inactive.  I just tried completing removing the relationships but still no joy.

[Lodan]

Hold out.

I might have sorted it.  I have started duplicating the tables and creating new measures so its fresh without any dependenices and it has started to look better.  Will update once complete.

Thanks

[Lodan]

That got me closer.  Now my totals correspond to each other but I can't seen to get the yearly total to be a sum of the NRR's.

[johnt75]

Can't immediately see what the problem is. Can you share a PBIX with any confidential info removed ? You could upload to OneDrive or Google Drive or similar.

[Lodan]

Yeah I had the same thought.  I will re-build it with demo data and see how that goes then upload the PBIX.  

I am away with work today so might take a few days.