Changes Requests

[Anonymous]

Hi 

Im trying to create a report which gives me changes requests that were done on ServiceNow for This Week. In ServiceNow I selected the filters as Planned Start Date on This Week and Planned End Date on This Week. So when I run the report then this takes up data from Monday to Sunday for the current week which is what I want to show in PowerBi as well.

I have already imported the data into PowerBi. I wanted to show the Approved Changes where Planned Start Date on This Week and Planned End Date is This week. Kindly help.

Imported Fields on PowerBiFilters in ServiceNow

[Anonymous]

Can someone tell me how I can do this on PowerBi to get the results needed

1.Change backlog growth :- The number of closed changes subtracted from the number of new changes.

2. Average age of open changes :- The average amount of time a change has been open.

3. % of urgent changesPercentage of urgent changes. Is a formula: Number of new changes classified as urgent / Number of new changes * 100

4. Average close time of changesThe average amount of time it has taken to close a change

Below is the data from PowerBi

[V-lianl-msft]

Hi @Anonymous ,

Based on your description. Do you want to judge whether the start and end date is in the current week?

Measure = 
var S_This_Week = WEEKNUM(MAX([Planned Start Date]),1) = WEEKNUM(TODAY(),1)
var E_This_Week = WEEKNUM(MAX([Planned End Date]),1) = WEEKNUM(TODAY(),1)
return S_This_Week&&E_This_Week

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the PBIx file. Unforturnately my PBI version is not updated but thats okay. I can check the formula you shared.

Yes, so I have a filter in ServiceNow as share above so when I actually extract today as This week it shows me changes that are part of current week which is Monday to Sunday so if I run it today it will be from 3rd May 2021 to 9th May 2021. So thats how I want to pull it and the other charts would interact accordingly.

Also 2 other things :-

1. I have pulled up the complete change request table from Snow and the data is present on PowerBi. there is a field called "Prority" when I first pulled up the whole table and pulled the field into the visualization pane it showed me High, Medium, Low etc but after sometime it shows me blank. I tried to refresh the data but nothing works. When I checked the field I can see all the details in it and nothing is blank in the column. its only when I pull it in the visualization nothing comes up. Can you help?

2. Im trying to build a dashboard that would give me month on month and week over week changes etc and percentage of change request with the total etc something similar what we see in this video. Can you help with some basic things I should be adding from this that can be done. Like Percentage of successful changes etc

https://www.youtube.com/watch?v=q96bCfDPqr0

Thanks in advance for your help!

[amitchandak]

@Anonymous , You can create a date table and join it with the planned start date and planned end date this and can use userelationship for inactive join say planned end date

first create date without timestamp like

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

Join to date table

measures

This Week planned = CALCULATE(countrows(Project) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

This Week end= CALCULATE(CALCULATE(countrows(Project) , USERELATIONSHIP ('Project'[Planned end date], 'Date'[Date])),  ) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

[Anonymous]

Hi Amit,

Thanks for your reply.

I have already connected the Planned start date to the date table as primary connection and end start date as secondry one with the date table.

Date Table CreatedConnected to Date Table

In the below measure are you saying CountRows (Project) your talking about the ChangeRequest Table in my scenario right?

Also how do I create Week Rank Table?

Join to date table

measures

This Week planned = CALCULATE(countrows(Project) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

This Week end= CALCULATE(CALCULATE(countrows(Project) , USERELATIONSHIP ('Project'[Planned end date], 'Date'[Date])),  ) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))