Displaying number of tickets created/closed during current and last week

[TechR21]

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?

[mangaus1111]

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.

[TechR21]

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

[mangaus1111]

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

[TechR21 · Accepted 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

[amitchandak]

@TechR21 , Follow the common table approach from the blog and attached file

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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

[TechR21]

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

[amitchandak]

@TechR21 , You need to have a date table, The file and blog I shared have, how to do that .