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 Guys,
I am just starting out with PowerBi and I am looking to build a chart that visualises the number of tickets created by date and then represent the information as sum of tickets created per day of the week. That way I can get insights into what day's of the week on average are our busiest.
I currently have one query for tickets (pulling from ConnectWise) which contains date entered, and my second query is a rolling calendar where the date field is linked to the ticket date entered field.
Solved! Go to Solution.
Hi @glynwilks ,
I suggest use the following formula to create rolling calendar to contain weekday information:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),"DayOfWeek", FORMAT ( [Date], "dddd" ))
Then you can use the following measure to calculate average tickets created by weekday:
Measure = AVERAGEX(ALLEXCEPT('Calendar','Calendar'[DayOfWeek]),COUNT('Table'[Ticket]))
If it doesn't meet your requirements, please show us some sample data and your expected output.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @glynwilks ,
I suggest use the following formula to create rolling calendar to contain weekday information:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2020,12,31)),"DayOfWeek", FORMAT ( [Date], "dddd" ))
Then you can use the following measure to calculate average tickets created by weekday:
Measure = AVERAGEX(ALLEXCEPT('Calendar','Calendar'[DayOfWeek]),COUNT('Table'[Ticket]))
If it doesn't meet your requirements, please show us some sample data and your expected output.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks Dedmon,
I will give this is a shot shortly and let you know how I go.
@glynwilks , You can create a date calendar there you can create week related columns and analyse by week
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
This Week Avg= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))/
CALCULATE(distinctcount('Date'[Date]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week Avg= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
/
CALCULATE(distinctcount('Date'[Date]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |