Forum Discussion
Solved
SUM based on VAR value/ Subscription Revenue Total
I want to get a total revenue measure but am having issues. I have three tables Customers and Product and Date. Relevent Fields for Customer: Company Start Date Product ID A 1/1/2020 10 ...
Please try the measure below:
Revenue = SUMX ( Customers, VAR _startDate = Customers[Start Date] VAR _endDate = MAX ( __Dates[Date] ) VAR _productPrice = CALCULATE ( MAX ( 'Product'[Price] ), 'Product'[ID] = Customers[Product ID] ) VAR _dateDifference = DATEDIFF ( _startDate, _endDate, MONTH ) VAR _paymentMonths = IF ( _dateDifference >= 0, _dateDifference + 1, 0 ) RETURN _paymentMonths * _productPrice )
cengizhanarslan
Super User
Super UserPlease try the measure below:
Revenue =
SUMX (
Customers,
VAR _startDate = Customers[Start Date]
VAR _endDate = MAX ( __Dates[Date] )
VAR _productPrice =
CALCULATE (
MAX ( 'Product'[Price] ),
'Product'[ID] = Customers[Product ID]
)
VAR _dateDifference =
DATEDIFF ( _startDate, _endDate, MONTH )
VAR _paymentMonths =
IF ( _dateDifference >= 0, _dateDifference + 1, 0 )
RETURN
_paymentMonths * _productPrice
)It worked! Thank you so much cengizhanarslan. This feels like magic! If you have a moment to explain why this worked I'd love that, but you've done so much so I get it if you don't.
I did have to add a line above _product price that was:VAR productID = Customers[Product ID]
But after it worked beautifully!
- cengizhanarslan
I'm glad it worrked! Basically you need to give variables inside the SUMX iteration because otherwise it does now obey the row context within the visual.
