Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    8 years ago

    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

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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!

  • NipponSahore's avatar
    NipponSahore
    Resolver II
    8 years ago

    Create a measure as 

     

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