Weekly In Progress & filters

[aameen1515]

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!!

[v-zhenbw-msft · Accepted solution]

Hi @aameen1515 ,

We can create a measure and name slicer to meet your requirement.

1. Create a date table that has no relationship with Non RFx1 table.

2. Then we can create a measure based on your original calculate column.

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

3. Create a slicer using BuyerName and put the BuyerName to Filters on this page, then configure the BuyerName is not blank.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[aameen1515]

Hi @v-zhenbw-msft v-zhenbw-msft

Can you help resolve my latest query? Really appreciate it!

Cheers!

[v-zhenbw-msft]

Hi @aameen1515 ,

This seems to be a new topic.

We suggest that you restart a new thread.

Because the two content under the same title will make it difficult for other users to find the corresponding issue. 

If you start a new thread and describe your issue clearly, more people will find your issue and provide you with a better answer.

Best regards,

Community Support Team _ zhenbw

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

[aameen1515]

Hi

This worked great! You are awesome.

Now I have 2 additional queries as I am now on the next step.

1) Now that I have the Weekwise InProgress for each buyer, can you help me & share a way to create a waterfall graph to show the week over week change of InProgress? i.e. red if it is increasing & green if it is decreasing?

2) I also want to create a table/matrix as in the screenshot below as on overview. This table would just have Current week InProgress & last week InProgress. The next column should show the "change" week over week for each buyer instead of showing a total. ALso if possible the change number could follow the conditional formatting of upwards error & red color if the week over week InProgress is increasing & green & downwards arrow if decreasing & yellow if no change.

Also, if theres an automatic way to just show last 2 weeks column always instead of me choosing it on the filters manually. (as I have done for week 27 & 28)

[rajulshah]

Hello @aameen1515,

I am unsure about my understanding of your issues. It would be better if you can share a sample file to understand the issue better.

Thanks.