Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCreate a measure as
Revenue3Months = calculate(sum(table[revenue]),filter(table,datesbetween([invoice Date], eomonth(today(),-4),eomonth(today()-1)))
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Perfect! I've been looking for a solution all day on this. Thank you so much!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |