Help me improve this recurring revenue measure
I have created the following measure to calculate recurring revenue: Maintenance Amount = VAR MinDate = MAX(SELECTEDVALUE('Recurring Revenue'[Recurring Start Date]),MIN('Date'[Date])) VAR MaxDate = MIN(SELECTEDVALUE('Recurring Revenue'[Recurring End Date]),MAX('Date'[Date])) VAR MinMonth = IF(MinDate > MaxDate,BLANK(), DATEDIFF( MinDate, MaxDate, MONTH ) + 1 ) RETURN SUMX( 'Recurring Revenue', MinMonth * 'Recurring Revenue'[Monthly Recurring Amount] ) Basically, I want to do a row-wise calculation in my "Deferred Revenue" table based on the current date filter context. My situation is that I want to be able to see the recurring revenue even if the invoice date (the field used to relate to the date table) is outside of the current filter context, I have gone around this by creating a calculated table specifically for deferred revenue. The measure currently works in a very limited context: if I create a table with the Start Date and the End Date it will give me the correct total for a given row. However, it will not calculate the grand total and it appears blank if I put it into a column visual. How can I fix this? It would be incredibly helpful if anyone has an idea on how to accomplish this without using a separate table for the recurring revenue. I have the same columns in the "Revenue" table so I could simply choose to only calculate the rows that have recurring revenue if it were possible to ignore the current filter context.
Calculate Region revenue in a Country table
Hi I require the Dax to be able to calculate the region revenue instead of the country revenue in a matrix table by data set is as follows the table i'm trying to create in power BI is something like this where i display both the country Rev and the Region rev next to each other which i have managed to do with the following dax. Region Rev = CALCULATE(SUM('Dataset 1'[Revenue]),FILTER(FILTER(ALL('Dataset 1'),'Dataset 1'[Region]=SELECTEDVALUE('Dataset 1'[Region])),'Dataset 1'[Year]=SELECTEDVALUE('Dataset 1'[Year]))) However i cannot get it to calculate by industry and it is just giving me the total rev for the region, rather then by Industry by for that region. in theory all i want to do is stop the measure from filtering on Country and instead filter on the region for that country when it is displaying any values any help would be much appreciated!
