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.
Hi!
I'm trying to create a measure of 'Retained Customers' by counting customernumbers and filtering on min&max dates but I can't get it to work. Since I'm using a direct query, creating a new filter column is not an option.
This is among the formulas I tried to use:
# Retained Customers =
CALCULATE(
COUNT(f_Sales[CustomerSK]);
MAXX(f_Sales;[TransDt]<20161000);
MINX (f_Sales;[TransDt]>20161000)
)
Can someone help?
Solved! Go to Solution.
Hi @hannaegils,
1. What is your data source? It's still possible to create a column in my test -- SQL Server DB.
2. In your formula, the second and the third parameters will return errors.
3. It will return nothing even if the formula is good. If we add a "=" to the formula, it will be very clear.
a) MAXX(f_Sales;[TransDt]<=20161000) return 20161000
b) MINX (f_Sales;[TransDt]>=20161000) return 20161000
a & b without "=" returns nothing.
4. What are the conditions exactly? Maybe you can try it like this:
# Retained Customers = VAR maxValue = CALCULATE ( MAX ( f_Sales[TransDt] ); f_Sales[TransDt] < "Your Value 1" ) VAR minValue = CALCULATE ( MIN ( f_Sales[TransDt] ); f_Sales[TransDt] > "Your Value 2" ) RETURN CALCULATE ( COUNT ( f_Sales[CustomerSK] ); FILTER ( ALL ( f_Sales ); f_Sales[TransDt] > minValue && f_Sales[TransDt] < maxValue ) )
Best Regards!
Dale
Hi @hannaegils,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi @hannaegils,
1. What is your data source? It's still possible to create a column in my test -- SQL Server DB.
2. In your formula, the second and the third parameters will return errors.
3. It will return nothing even if the formula is good. If we add a "=" to the formula, it will be very clear.
a) MAXX(f_Sales;[TransDt]<=20161000) return 20161000
b) MINX (f_Sales;[TransDt]>=20161000) return 20161000
a & b without "=" returns nothing.
4. What are the conditions exactly? Maybe you can try it like this:
# Retained Customers = VAR maxValue = CALCULATE ( MAX ( f_Sales[TransDt] ); f_Sales[TransDt] < "Your Value 1" ) VAR minValue = CALCULATE ( MIN ( f_Sales[TransDt] ); f_Sales[TransDt] > "Your Value 2" ) RETURN CALCULATE ( COUNT ( f_Sales[CustomerSK] ); FILTER ( ALL ( f_Sales ); f_Sales[TransDt] > minValue && f_Sales[TransDt] < maxValue ) )
Best Regards!
Dale
Worked as a charm! Thanks you so much 🙂
Hi,
What kind of a date is 20161000? Share a dataset and show the expected result.
Hi,
The column "transdt" that contains the value 20161000 is formatted as a whole number in the dataset, although I have a connection to another table where I have the dates on different date formats.
/Hanna
Hi,
Share the link from where i can download your file.
Since you don't have access to our databases, a share of the file won't do any good here - will it?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |