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

     

     

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!