cancel
Showing results for
Did you mean:
mrainey Member

## Revenue Formula

I have this data: 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

## 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: 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``` Regards,

MFelix

Proud to be a Datanaut!

4 REPLIES 4
mrainey Member

## Formula Tweak

I have this formula: 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

## 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: 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``` Regards,

MFelix

Proud to be a Datanaut!

NipponSahore Member

## 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

## Re: Revenue Formula

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

Announcements #### 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. Learn the answers to some of the questions asked during the Amanda Triple A event. #### 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.  Top Kudoed Authors
Users Online
Currently online: 176 members 2,000 guests
Recent signins:
• basyaiban • JANDREASL • Taniya • Ethanhunt123 • Dee • danialsj • Madelein • TYTY • okaplan • farezmd • dkassian • XZENG13 • M-F • Breacher 