cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JP-Ronse Regular Visitor
Regular Visitor

Weeknum and other questions

Hi,

 

Given:

 

2018-01-13 15_37_03-PowerPivot for Excel - Book4.png

 

ReactionTime is a calculated column: =value([Creation Time])-value([Event Date])

WeekNum: =WEEKNUM([Creation Time];21)

 

  1. The DAX reference does not mention 21 as parameter, only 1/2 but it does make a difference, can I assume that the same rules apply as for the Excel formula.
  2. I would like to know how many Px incidents where in a week (x=> 1...4)
  3. I would like to know how many Px incidents where logged within the SLA. The SLA is the ReationTime.

In words,  say that the SLA for a P1 incident = 15 minutes, I want to know the percentage of P1 incidents logged within the SLA.

 

Next, I want to calculate the same figures over the last 5 weeks, the week starts on Monday.

 

Thanks & kind regards,

 

JP

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
JP-Ronse Regular Visitor
Regular Visitor

Re: Weeknum and other questions

Hi Ashish,

 

I found a solution to all my questions.

 

ISO using WeekNum in PQ I used WeekNum in PP and that function has the ISO option.

 

The easiest way to group 5 running weeks was using a slicer.

 

Kind regards,

 

JP Ronse

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Weeknum and other questions

Hi,

 

Could you share a small sample dataset and show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JP-Ronse Regular Visitor
Regular Visitor

Re: Weeknum and other questions

Hi Ashish,

 

Glad to meet you here.

 

The data is business confidential, although I can remove most confidential parts, I don't want to take the risk and share it because there is always a way to trace back. Sorry.

 

The good news is that I managed to solve most of my questions by using PQ & PV. It took me some time to get here and, I know,  you would have done it in few minutes but struggling is in most cases the best way to learn.

 

The remaining question is how I can group the WeekNum in a running 5 weeks.

 

The exercise is a SLA (Service Level Agreement) calculation, so I managed to mark in PQ the P1 incidents and calculate if that incident was taken up within the agreed time to do so. Managed that.

 

But my manager also wants to have that figure over 5 running weeks. IOW, say we are now week 7, give the figure over week 3, 4, 5, 6, 7 and next week over 4, 5, 6, 7, 8.

 

Any idea?

 

Kind regards,

 

JP Ronse

 

 

 

Super User
Super User

Re: Weeknum and other questions

Hi,

 

Glad to see you here as well.  I will need some data to work with.  Please share a dummy dataset and also show your result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JP-Ronse Regular Visitor
Regular Visitor

Re: Weeknum and other questions

Hi Ashish,

 

I found a solution to all my questions.

 

ISO using WeekNum in PQ I used WeekNum in PP and that function has the ISO option.

 

The easiest way to group 5 running weeks was using a slicer.

 

Kind regards,

 

JP Ronse

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 149 members 1,963 guests
Please welcome our newest community members: