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:

CompanyStart DateProduct ID
A1/1/202010
B1/1/202111


Product:

PriceID
25010
11011


I have a DAX expression currently that finds the price and how many months of activity and gets me revenue based per company and per product, but not a total revenue. 

I want to be able to get all revenue across all companies and products and then filter with my charts later.

Here is the expression I currently have. 

Revenue =
    VAR company = SELECTEDVALUE(Customers[Company])
    VAR productID = SELECTEDVALUE(Customers[Product ID])
    Var firstPayment = 1
    Var startdate = SELECTEDVALUE(Customers[Start Date])
    Var endDate = MAX(__Dates[Date])
    VAR productPrice = CALCULATE(
        VALUES(
            'Product'[Price]
        ), 
        'Product'[ID] = productID
    )
    VAR dateDifference = DATEDIFF(SELECTEDVALUE(Customers[Subscription Date]),
                endDate,
                MONTH
            )
    VAR paymentMonths = IF(dateDifference >= 0, dateDifference+firstPayment, 0)
    Return SUMX(Customers, paymentMonths*productPrice)

How can I get a regular SUM() to work with it. I know some people got CALCULATETABLE(SUMMERIZE()) to do this, but when I tried that I kept getting error messages. 

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
)

3 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    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.