Forum Discussion

Lodan's avatar
Lodan
Icon for Helper II rankHelper II
1 year ago
Solved

MRR Revenue Growth

Hello,

 

You helped me out previously (thanks a lot for this) when I needed to be able to track revenue by month / year but cumulating via start and end date of that revenue.  That is in the PBIX file in the folder linked.  In effect it looks like this:

 

MRR Revenue is when it was sold.

MRR Revenue2 sums up the revenue for each month after it is sold until it's end month.  It works well and uses this measure:

 

MRR Revenue2 = 
VAR MaxDate = CALCULATE( MAX( 'Date2'[Date] ) )
VAR MinDate = CALCULATE( MIN( 'Date2'[Date] ) )
VAR Result = CALCULATE(
	SUM( 'CMD Items'[MRR$] ),
    'CMD Items'[Completed Date] <= MaxDate,
    'CMD Items'[End of Term]> MaxDate,
    REMOVEFILTERS( 'Date2' )
)
RETURN Result

 

 

 

However now I want to take it a step further and split it out into years but still doing the cumulative start / end date effect.  Then I would like to add up them for a period (say H2) so that I can compare the two H2's and give me a percentage growth between them.  

 

I don't think I explained that very well so I have exported the data into excel and done in excel what I would like to do in PowerBi (also in the attached).

 

In effect I want to build a table based on the above using month rather than month/year and with a measure or something that just does a year in turn like this:

 

 

Then with those year measures I can filter by last 6 months on both and turn it into a growth percentage like so:

 

The problem is (i think) because I am removing filters from the date table to make the cumulative effect work I then can't seem to work out how to filter it by year after that.

 

Any help would be appreciated please.

 

Thanks and regards

Power BI

  • I think I have just managed to get a step further:

     

    test 2024 = CALCULATE([MRR Revenue2],Date2[Year]=2024)

    I had the idea to use a measure and claculate to reference the other measure but filter it by year.  As you can see this worked.

     

    However if I then use these measure in a growth measure to get a percentage by filtering on the last 6 months like this:

     

    Growth = ([test 2025]-[test 2024])/[test 2024]

    It doesn't give me what I want because its not sum'ing them up.  I can't work out how to sum a measure.  I think maybe I need to create a virtual table of those measures but couldn't work out the formula:

     

    Hoping someone has an idea please.

  • I feel like I am just spamming at this point but I 'might' have figured it out.  Its late so will check in ernest tomorrow but this seems to be working so far:

     

    tester 2024 = sumx(VALUES(Date2[Month]),[test 2024])

3 Replies

  • Updated PBIX hadn't saved so I just re-uploaded it.

  • I think I have just managed to get a step further:

     

    test 2024 = CALCULATE([MRR Revenue2],Date2[Year]=2024)

    I had the idea to use a measure and claculate to reference the other measure but filter it by year.  As you can see this worked.

     

    However if I then use these measure in a growth measure to get a percentage by filtering on the last 6 months like this:

     

    Growth = ([test 2025]-[test 2024])/[test 2024]

    It doesn't give me what I want because its not sum'ing them up.  I can't work out how to sum a measure.  I think maybe I need to create a virtual table of those measures but couldn't work out the formula:

     

    Hoping someone has an idea please.

    • Lodan's avatar
      Lodan
      Icon for Helper II rankHelper II

      I feel like I am just spamming at this point but I 'might' have figured it out.  Its late so will check in ernest tomorrow but this seems to be working so far:

       

      tester 2024 = sumx(VALUES(Date2[Month]),[test 2024])