Forum Discussion
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
- Anonymous7 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
- AnonymousNot 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- AnonymousNot 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- v-juanli-msft
Community Support
:smileyhappy:
Regards
Maggie