cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Revenue Formula

I have this data:

1.PNG

 

I've attempted to build a formula that takes the last 3 months of revenue for a company while excluding the current month, but it isn't coming out correct. The [Annual Revenue] is just the sum of those 3 months I've highlighted multiplied by 4. (750.60 + 729 + 749.06) *4.

Displaying it this way is the result I'm looking for, I just need it to be in a formula format. How can I do so? Everything I've tried so far hasn't worked. I need it to be dynamic so next month, it will give me the sum of ((Mar-May) *4) for all companies.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Revenue Formula

Hi @mrainey,

 

Create the following measure:

Revenue 3 Months =
VAR Selected_Month =
    DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ); 1 )
VAR Selected_3_Months =
    DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 3; 1 )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] );
        FILTER ( ALL ( Revenue[InvoiceDate] ); Revenue[InvoiceDate] < Selected_Month );
        Revenue[InvoiceDate] >= Selected_3_Months
    )
        * 4

This will give you the calculation you need:

 

revenue.png

 

If you want to have a measure based on the slicer you should create  Calendar table without relation with your table and then use the following formula:

 

Revenue 3 Months SLICER = 
VAR Selected_Month =
    DATE ( YEAR ( MAX('calendar'[Date]) ); MONTH ( MAX('calendar'[Date]) ); 1 )
VAR Selected_3_Months =
    DATE ( YEAR (MAX('calendar'[Date]) ); MONTH ( MAX('calendar'[Date]) ) - 3; 1 )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] );
         Revenue[InvoiceDate] < Selected_Month &&  Revenue[InvoiceDate] >= Selected_3_Months)
    
        * 4

3«onths.gif

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
mrainey Member
Member

Formula Tweak

I have this formula:

1.PNG

 

It's designed to calculate the last 3 months of revenue. What I need to figure out how to do from here is:

1. Figure out how to calculate revenue for current companies. Currently, this will calculate for all companies even if for instance their last revenue date was in 2015. I'm only concerned with looking at the past 3 months and don't want old totals in my final grand total. I have a calendar table already setup, just wasn't sure how to match the 2 or if it was necessary.

 

2. How to get the formula to exclude the current month we are in. So some companies have revenue populated for May, but I only want to include May once the full month is complete. So for all companies, I'm looking for Feb-Apr revenue. Once June hits, it would then be Mar-May revenue etc. etc. Thanks!

Super User
Super User

Re: Revenue Formula

Hi @mrainey,

 

Create the following measure:

Revenue 3 Months =
VAR Selected_Month =
    DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ); 1 )
VAR Selected_3_Months =
    DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) - 3; 1 )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] );
        FILTER ( ALL ( Revenue[InvoiceDate] ); Revenue[InvoiceDate] < Selected_Month );
        Revenue[InvoiceDate] >= Selected_3_Months
    )
        * 4

This will give you the calculation you need:

 

revenue.png

 

If you want to have a measure based on the slicer you should create  Calendar table without relation with your table and then use the following formula:

 

Revenue 3 Months SLICER = 
VAR Selected_Month =
    DATE ( YEAR ( MAX('calendar'[Date]) ); MONTH ( MAX('calendar'[Date]) ); 1 )
VAR Selected_3_Months =
    DATE ( YEAR (MAX('calendar'[Date]) ); MONTH ( MAX('calendar'[Date]) ) - 3; 1 )
RETURN
    CALCULATE (
        SUM ( Revenue[Revenue] );
         Revenue[InvoiceDate] < Selected_Month &&  Revenue[InvoiceDate] >= Selected_3_Months)
    
        * 4

3«onths.gif

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Re: Revenue Formula

Create a measure as 

 

Revenue3Months = calculate(sum(table[revenue]),filter(table,datesbetween([invoice Date], eomonth(today(),-4),eomonth(today()-1)))

mrainey Member
Member

Re: Revenue Formula

@MFelix Perfect! I've been looking for a solution all day on this. Thank you so much!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 176 members 2,000 guests
Please welcome our newest community members: