Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |