Revenue Formula

[Anonymous]

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.

[NipponSahore]

Create a measure as 

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

[MFelix · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

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

[Anonymous]

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!