Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

If statement to sum values based on date on two tables

Hello I am trying to create a measure to sum values with an if statement across dates.. At present I have three tables (see below for more details) - The dax I am trying to write would sum the value of subscribers based on date, if the date selected is greater that the highest date in the subscriber date, then sum the subscribers in the customers table

 

Thanks in advance


Ideally, it would look like this

Date:                         customer number            subscribers (the measure I am trying to make)

March 2019.              1                                      10 (Historic Value from Subscribers table)
April 2019                 1                                       12 (Historic Value from Subscribers Table)
May 2019                  1                                       13 (Current Value from Customer Table)

 

To sum the number historical subscriptions. I am using the following dax:
historical subs =
CALCULATE(
          SUM(Subscribers[subscribersCount]), 
          USERELATIONSHIP(Subscribers[date],'Date'[Date])
)


To get the sum of current subscriptions. I am using the following dax:
Current Subs =
         Sum(customers[subscribers)

I have tried adding both of these as variables then using an if statment:

If (date[date] > subscribers[date], current subs,historical subs)

Table: Subscribers (has historical subscriber numbers)
Columns:

  • Date - Format (4/1/2019 all dates are at the first of the month)
  • CustomerID
  • Subscribers

Table: Customers (has current subscriber numbers)
Columns:

  • CustomerID
  • Customer Name
  • Subscribers (current number)

Table: Date (has current subscriber numbers)
Columns:

  • Date - Format: 8/1/2019
  • Months - Format: April 2019
  • Anonymous's avatar
    Anonymous
    7 years ago

    Found my own solution to the issue.. 

     

    It was more simple than I was making it out to be. 

     

    Subs (other) = 
    
    var histsubs=
        CALCULATE(
            SUM(Subscribers[subscribersCount]),
            USERELATIONSHIP(Subscribers[date],'Date'[Date])
        )
    
    var currsubs =
        CALCULATE(
            sum(Company[Active Subscribers]),
            'Date'[Date],
            USERELATIONSHIP(Company[OLM Company ID],Subscribers[customerId])
        )
    var datecompare =
        IF(
            ISBLANK(histsubs),currsubs,histsubs
         )
    
    RETURN
    datecompare

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Using the following dax measure gets me really close..

     

    Subscribers = 
    
    var maxsubdate = ENDOFMONTH(Subscribers[date])
    var seldate = STARTOFMONTH(ALLSELECTED('Date'[Date]))
    
    var datecompare =
        IF(
            seldate > maxsubdate,0,1
         )
    
    var histsubs=
        CALCULATE(
            SUM(Subscribers[subscribersCount]),
            USERELATIONSHIP(Subscribers[date],'Date'[Date])
        )
    
    var currsubs =
        CALCULATE(
            sum(Company[Active Subscribers])
        )
    
    var tbl=
                if(datecompare=1,
                CALCULATE(currsubs),
                CALCULATE(histsubs)
                )
    RETURN
    tbl


    As you can see though from the screenshot below, it still does not populate the "current" value from customer table


    • Anonymous's avatar
      Anonymous
      Not applicable

      Found my own solution to the issue.. 

       

      It was more simple than I was making it out to be. 

       

      Subs (other) = 
      
      var histsubs=
          CALCULATE(
              SUM(Subscribers[subscribersCount]),
              USERELATIONSHIP(Subscribers[date],'Date'[Date])
          )
      
      var currsubs =
          CALCULATE(
              sum(Company[Active Subscribers]),
              'Date'[Date],
              USERELATIONSHIP(Company[OLM Company ID],Subscribers[customerId])
          )
      var datecompare =
          IF(
              ISBLANK(histsubs),currsubs,histsubs
           )
      
      RETURN
      datecompare