Forum Discussion
bdsimpsn
3 years agoFrequent Visitor
Calculating WTD, MTD, Days Since Opened, Resolution Time
I'm quite new to PowerBI have been tasked to create a dashboard to show service desk ticket stats, and need some assistance with formulas to calculate the following:
- Tickets opened this week (WTD)
- Tickets opened last week
- Tickets opened this month (MTD)
- Tickets opened last month
- Tickets opened this quarter
I have got tickets opened today and yesterday working using:
Today = Calculate(
DISTINCTCOUNT('Tickets'[Number]),
FILTER(ALL('Tickets'), 'Tickets'[Opened] >= TODAY()))
Yesterday = Calculate(
DISTINCTCOUNT('Tickets'[Number]),
FILTER(ALL('Tickets'), 'Tickets'[Opened] >= TODAY()-1))I also have 'days since opened' and 'days since updated' using the below formula, but need to count only working days. I am wanting to use the NETWORKDAYS formula but can't get my head around structuring this.
Days Since Opened = INT(Today()-[Opened]) Days Since Updated = INT(Today()-[Updated])
And lastly, I'm needing to calculate the resolution time using the time closed and time opened. I'm assuming this will be using the DATEDIFF formula, but haven't been able to get this working either.
Would love your assistance with these please!
1 Reply
- Greg_Deckler
Community Champion
bdsimpsn Try:
Days Since Opened = NETWORKDAYS(Today(),[Opened]) Days Since Updated = NETWORKDAYS(Today(),[Updated]) Days Open = ([Closed] - [Opened]) * 1.