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 all,
I am trying to do projection of queries for EOM.
Based on last 2 months and based on current month.
I cannot figure out point 3.
We have vastly different number of queries based on workday,
So what I am trying to achieve is
1. take (number of queries by weekday (monday,tuesday...)/ average of week) from last 2 months
var last_2_months = IF(MONTH(today()) <= 1,
DATE(YEAR(TODAY())-1,12+MONTH(TODAY())-1,1),
DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))
return
CALCULATETABLE(
SUMMARIZE('Date table','Date table'[DayOfWeekName],"days",COUNT('Date table'[Date]),"avg_queries", [Queries]/[Avg per week Queries]),'Date table'[Date]> last_2_months)
that returns me how every weekday is compared to average of week
2. based on date slicer count number of days by weekday
(so if current selection is from monday including to next tuesday including, we have 2x monday, 2x tuesday and rest 1x)
3. calculate how current selection compares to weekday split
(average of monday, monday, tuesday, tuesday, wednesday, thursday...)
rest I can figure out after that
4. (number of days til EOM)*(count number of days by weekday) *(point 3 for EOM) * (average queries per day based on date slicer)
Thanks a lot for any help, mostly I cannot figure out point 3.
Hi @Anonymous ,
“
calculate how current selection compares to weekday split
(average of monday, monday, tuesday, tuesday, wednesday, thursday...)
”
Please provide a sample data that can be copied.
Also please show what you want to return in the visual based on the slicer.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |