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 have a table Visits with Customer ID (Customer ID FK] and multiple customer visits with visit date (Start Date).
This measure works beautifully when there is no date slicer on the report. It brings number of days between customer's first visit date and today.
Now, I would like the users to have an option to select a date instead of using Today. I want to calculate first visit's date against selected date on the slicer.
This measure works but it always brings the value of 1 because min(Visits[Start Date) is taken from the slicer. So, there is just one day difference (or 0 depending on date format) between min date and selected date.
CustomerRelationship = SUMMARIZE(Visits,Visits[Customer ID FK], "Relationship",CALCULATE(DATEDIFF(min(Visits[Start Date]), SELECTEDVALUE('Date'[Month Year]),DAY)))
Any ideas? You help would be appreciated.
Thaanks amitchandak.
I applied this but the message I have now is, the expression specified in the query is not a valid table expression.
@kris_talikowski , Create a measure and use that with customer from original table
AverageX(
SUMMARIZE(Visits,Visits[Customer ID FK], "_Relationship",DATEDIFF(min(Visits[Start Date]),selectedvalue(Date[Date]),DAY)), [_Relationship])
You can use SUMX inplace of Averagex or You can use maxx(allselected(Date), Date[Date]) in place of selectedvalue(Date[Date])
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 |
---|---|
98 | |
98 | |
80 | |
76 | |
66 |
User | Count |
---|---|
135 | |
109 | |
104 | |
83 | |
73 |