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.
I want to create a daily enquiry report which would provide an enquiry count from the last date’s data in the dataset which is then automatically compared to the historical average of that day’s weekday. So if Power Bi counts enquiries on Wednesday, then the system would automatically calculate the historical enquiry count of Wednesday (average per day count). It would be easy to achieve this goal by using a slicer that has Weekday options on it, but I am wondering whether it would be possible to create a measure which would check the last date’s weekday on the dataset and then calculate that weekday’s average daily enquiry count?
The measure I am using to calculate daily enquiries is as follows:
Enquiries LastDate Count = CALCULATE([Total Enquiries Count],LASTDATE(Enquiries[EnquiryDate]))
I have tried to figure out a solution without a success. I would be grateful if anybody could point me in the right direction.
Here is my example data:
https://goo.gl/AjLwya
Solved! Go to Solution.
try this code:
Measure = VAR TheDate = MAX(Enquiries[EnquiryDate]) VAR DayOfWeek = WEEKDAY(TheDate,2) VAR SameDays = FILTER(Dates,Dates[WeekDay]=DayOfWeek && Dates[Master Date]<TheDate) VAR SameDaysEnquires = ADDCOLUMNS(SameDays,"Val",[Total Enquiries Count]) RETURN AVERAGEX(SameDaysEnquires,[Val])
it calculates the average for all the same days that are before the current date
try this code:
Measure = VAR TheDate = MAX(Enquiries[EnquiryDate]) VAR DayOfWeek = WEEKDAY(TheDate,2) VAR SameDays = FILTER(Dates,Dates[WeekDay]=DayOfWeek && Dates[Master Date]<TheDate) VAR SameDaysEnquires = ADDCOLUMNS(SameDays,"Val",[Total Enquiries Count]) RETURN AVERAGEX(SameDaysEnquires,[Val])
it calculates the average for all the same days that are before the current date
Thank you so much, Stachu, for your elegant solution 🙂
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |