Forum Discussion

aameen1515's avatar
aameen1515
Helper I
6 years ago
Solved

Weekly In Progress & filters

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 Reques...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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's avatar
    aameen1515
    Helper I
    6 years ago

    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)

  • aameen1515's avatar
    aameen1515
    Helper I
    6 years ago

    Hi v-zhenbw-msft v-zhenbw-msft

     

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

     

    Cheers!

    • v-zhenbw-msft's avatar
      v-zhenbw-msft
      Community Support
      6 years ago

      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.