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,
Im trying to calculate how many tickets are created and closed during current and previous week
I have created 1 measure which calculates the total of all tickets:
Counttickets = COUNT('tickets'[id])
in the same table "tickets" there is a creationdate and a closingdate
Now i want to calculate how many tickets are
1) created during this week,
2) created during previous week,
3) closed during this week,
4) closed during previous week
Whats the easiest way to do this?
Solved! Go to Solution.
at the moment there is no specific date table in the dataset. Some tables have creationdates of tickets, and closuredates, i work with those. So any date table should be created
Hi @TechR21 ,
see if my pbi file makes sense in your case.
https://1drv.ms/u/s!Aj45jbu0mDVJi08Qnm2B6G87NGzB?e=Hhk0gF
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Im gonna try this on my full dataset.
Questions: What if I want to add a weekcolumn in my "incidents" table with weeknumbers, when a week is calculated from a friday 17:00 till the next friday 16:59:59, so I know in which week a specific ticket is created/closed
The week column is better in the date table. If you have a Date/Time column in your fact table is getting harder because you need a time table as well. If you send me a pbi file with sample data I can try to find a solution
at the moment there is no specific date table in the dataset. Some tables have creationdates of tickets, and closuredates, i work with those. So any date table should be created
@TechR21 , Follow the common table approach from the blog and attached file
Then use this week or week concept on you measures open and closed
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
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)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
WeekDay = weekday([Date])
These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
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...
https://www.youtube.com/watch?v=pnAesWxYgJ8
what do you mean with ('Table'[Qty]) in:
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
also what column do you mean when you write: "'Date'[Date]"?
I have one table named "incidents"
columns:
- id
- creationdate
- closingdate
and to count all the tickets i created a measure:
Counttickets = COUNT('incidents'[id])
Please find a sample of the data here https://1drv.ms/u/s!Aos-gKWrjzqNb0QSKQZBb71oJBg?e=UnqIyy
the data that Im using changes and gets updated on a daily base. Ideally i want to create different measures that calculates:
1) the tickets created during previous week
2) the tickets created during week before the previous week (all older tickets are not important)
3) tickets closed during previous week
4) tickets open at the end of previous week
@TechR21 , You need to have a date table, The file and blog I shared have, how to do that .
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |