Weekly total of "in progress"

[aameen1515]

Hi All

I have a dataset which has Issue received date & a corresponding status as "In progress".

Once the issue is resolved the Resolution date field gets populated & status changes to "Resolved"

I want to plot the bar chart for Weekly In Progress Count. i.e. in the week when an issue is resolved it is not counted as "In Progress" that week but until the issue is resolved each week that issue will be considered as "In Progress"

Sample data below

Status	Issue received date	Issue received week	Issue resolved date	Issue resolved week
In Progress	01-Jan-20	1	24-Feb-20	9
Resolved	03-Feb-20	6	28-Mar-20	13
In Progress	06-Mar-20	10	29-Apr-20	18
In Progress	30-Jan-20	5	24-Mar-20	13
Resolved	06-Feb-20	6	31-Mar-20	14
Resolved	10-Mar-20	11	03-May-20	19
Resolved	11-Apr-20	15	04-Jun-20	23
In Progress	09-Mar-20	11	02-May-20	18

In this table above the first row entry should show as In Progress for next 8 weeks in the bar chart.

Thus on a weekly basis all In Progress data should be shown as a sum total of In Progress each week.

I am new to power bi & if this is a dumb query, I apologize in advance.

Thanks

[ryan_mayu]

@aameen1515 

you need to have a calendar table and create weeknum and inprogressnum column

weeknum = WEEKNUM(datetime[Date])

inprogressnum = CALCULATE(COUNTROWS('table'),FILTER('table','table'[Issue received week]<=datetime[weeknum]&&'table'[Issue resolved week]>=datetime[weeknum]&&'table'[Status]="In progress"))

Then create a bar chart and set the average value for inprogressnum

Hope this is helpful.

[aameen1515]

Hi @ryan_mayu 

I am getting the following error:

The Rfx main table data is as below:

it also has the req received week & req executed week columns. These two columns show up in data table (as I created them) but I dont see these 2 columns in POwer query editor.

I am kind of a little lost. Any help would be great @ryan_manu

Thanks

[ryan_mayu]

@aameen1515 

datetime is the table I created , please change to your table name and weeknum is also the column in the datatime table.

'yourtable'[weeknumcolumn in your table]

[aameen1515]

Thanks a lot for clarifying that @ryan_mayu . You are awesome!

However, I have a couple of queries more:

1) Since the weeknum pulls the week number irrespective of the year, this calculation is giving me an output basis the numeric week number only. e.g. there are entries from same week in the prior years as well. Thus it is considering all as same only. Is there a way we can put a differentiator for years?

2) In the example above i could only get output once I removed the "In Progress" condition as it was giving me totally blank output with In Progress. However, the status does have the exact same wordings in the data set:

Status options in RFxMain table as below

Thansk a ton!

[ryan_mayu]

@aameen1515 

For the 1st question, I suggest you use yearweek instead of weeknum. e.g yearweek: 201701,201852,201922

For the 2nd question, it's better to share your sample data? I think there are differences  between your real data and the sample data you shared in the first message of this post.

[aameen1515]

Hi @ryan_mayu

Thanks for your response.

I could not find yearweek function in power bi editor. However I have dealt with that issue separately & taking the absolute dates instead of weeknums.

Secondly for In progress query, i figured the reason as well. It was showing blank data for In Progress is because if there is an executed date populated the status would be "Executed" & NOT "In Progress", if the req is still In Progress then the executed date would be left balnk. So i tweaked the formula like explained below. There could be an easier way to do it but i did in a longer simpler way.

Now here is the situation below:

Hi All

I need to plot the “In Progress” reqs info in a bar chart by week.

My source table has following info.

Non RFx1 is the table name

req recvd dt NR	Executed Date NR	NR Request Status	BuyerId	BuyerName	Requisition#
01-Jan-2020	-	In Progress	2345	John Doe	3478
05-Feb-2020	12-March-2020	Executed	3215	Alex Gomez	4521
09-Mar-2020	-	In Progress	2345	John Doe	8975
14-Mar-2020	-	In Progress	-	-	8975

The reqs are identified as “In Progress”, if we have Req status as “In Progress” & the executed date is left blank. Once the req is executed, executed date is populated & status changed to “Executed”. There is also a Buyer column which identifies the buyer name who is currently actioning the requisition. If no buyer has been assigned the buyer name is left blank but the way the system is built the status is auto populated as soon as the requisition is created in the system as in row 4 of the table above.

For reporting the “In Progress” trends over the weeks I have created a Calendar table with the help of which I am able to plot the In progress over weeks.

I have used these formulas in creation of InProgress columns in Calendar table

Non RFx1 InProgress = CALCULATE(COUNTROWS('Non RFx1'),FILTER('Non RFx1',('Non RFx1'[req recvd dt NR])<='Calendar table'[Date] && ('Non RFx1'[Executed Date NR])>='Calendar table'[Date]))+ CALCULATE(COUNTROWS('Non RFx1'),FILTER('Non RFx1',('Non RFx1'[req recvd dt NR])<='Calendar table'[Date] && ('Non RFx1'[NR Request Status]="In Progress" )))

Table is as below:

A snapshot of the graph is as below:

But here I have two issues:

• I am not able to filter out the rows which have blank entries against Buyers Id or buyers name . i.e. since no buyer is assigned to the req it is not considered In Progress. Can you help me how to put that condition while filtering the info. I tried some varied logics but could not figure out.

• I also need to be able to filter the data basis the buyers name. e.g. I should be able to know how is the In Progress trend for John Doe, or for Alex Gomez

Thanks in advance!!

[ryan_mayu]

@aameen1515 

For the week over year, you can try this.

yearweek = year('datetime'[Date])&right("0"&WEEKNUM(datetime[Date]),2)

inprogressnum = 
CALCULATE(COUNTROWS('table'),FILTER('table','table'[Issue received yearweek]<=datetime[yearweek]&&'table'[Issue resoolved yearweek]>=datetime[yearweek]&&'table'[Status]="In progress"))

 However, if you want to filter by buyers, maybe my solution is not the best for you. So far, I dont have any other solution for this.

[aameen1515]

* Correction to the table

Status	Issue received date	Issue received week	Issue resolved date	Issue resolved week
In Progress	01-Jan-20	1
Resolved	03-Feb-20	6	28-Mar-20	13
In Progress	06-Mar-20	10		18
In Progress	30-Jan-20	5		13
Resolved	06-Feb-20	6	31-Mar-20	14
Resolved	10-Mar-20	11	03-May-20	19
Resolved	11-Apr-20	15	04-Jun-20	23
In Progress	09-Mar-20	11	02-May-20	18

In this table above the first row entry should show as In Progress for each week till date in the bar chart.

for second row it show as a count In Progress from Week 6 to week 13

Thus on a weekly basis all In Progress data should be shown as a sum total of In Progress issues each week.

[amitchandak]

@aameen1515 , see if this can help

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

[aameen1515]

Hi Amit

would you be able to answer this for me?

if you can follow this thread below that would be great.

how can i have a filter for buyers as well.

really appreciate your support!