Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
179 | |
108 | |
104 | |
71 | |
70 |