Forum Discussion

Croshay's avatar
Croshay
Frequent Visitor
5 months ago
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 ...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    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
)
Croshay's avatar
Croshay
Frequent Visitor
5 months ago

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's avatar
cengizhanarslan
Icon for Super User rankSuper User
5 months ago

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.