Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Table: Customers (has current subscriber numbers)
Columns:
Table: Date (has current subscriber numbers)
Columns:
Solved! Go to Solution.
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
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
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
Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |