Filtering around the "Active" Value in a chart

[Anonymous]

Hello,

I have a Matrix of Orders that shows year and month of the orders as columns, the region of the orders on the rows, and a measure as a value.

I need the measure to be all orders that were committed before the month being displayed in the matrix and completed after the month. 

For example, let's pretend, we are looking at the month of May, 2018 in the Matrix.  That month should look at all the commit dates that came in months before it, and the realized dates that came in months after it.  If we look at the table below, the result in the Matrix in Power BI should show 8.

Production Year-Month Commit Production Year-Month Realized

201804	201806
201804	201807
201804	201806
201804	201806
201804	201806
201804	201806
201804	201808
201804	201809

I have a formula that does half the filter but I can't for the life of me figure out how to put the 2nd part of the filter and get it working in a Matrix as expected.

Production Test = CALCULATE(COUNT('Order Book'[Production On Time]), 
                                FILTER('Order Book', SELECTEDVALUE('Order Book'[Production Year-Month Commit]) > 'Order Book'[Production Year-Month Commit])
                                )

I'm using the "SelectedValue" function so I can reference which month the Matrix is calculating currently against the entire Order Book table.  I'm assuming this is incorrect. 

But, if this is correct, I can't place a second filter in here where I write something like:

Production Test 3 = CALCULATE(COUNT('Order Book'[Production On Time]), 
                                FILTER('Order Book', SELECTEDVALUE('Order Book'[Production Year-Month Commit]) > 'Order Book'[Production Year-Month Commit] &&
                                                     SELECTEDVALUE('Order Book'[Production Year-Month Realized]) < 'Order Book'[Production Year-Month Realized])
                                )
                              

I'll just get no results.  My matrix is the following with the first formula (Production Test) that I pasted above:

Essentially, I need a measure that will show 8 for the month of May where it performs both filters I need and not just the 1st filter.  Any suggestions to what I'm doing wrong here?

Thanks!

[v-piga-msft]

Hi @Anonymous ,

If I understand your sceanrio correctly that you have two slicers for Production Year-Month Commit and Production Year-Month Realized? 

By my tests, I cannot reproduce your scenario, if it is convenient, could you share your data sample and your desired output so that I could have a test on it.

Best  Regards,

Cherry

[Anonymous]

Hi @v-piga-msft ,

I can't seem to attach a file to this message but I'm placing a Dropbox link which hopefully you should be able to access.  It is a sample file that uses a sample dataset and explains my problem which I tried to explain here.  If you can take a look and let me know if there is any additional information I would need to provide, that would be great!

https://www.dropbox.com/s/rj6btudom7tqijd/Sample%20Data.pbix?dl=0

Thank you.

[Anonymous]

Anyone have any help on this?  I still haven't been able to figure it out.  My last message with my sample file explains the issue much better.

Thanks.

[Anonymous]

Still trying to see if anyone has a suggestion for this. Been stuck on this problem for weeks now and it seems like it would be a simple solution.

[Anonymous]

Hi @v-piga-msft ,

Did you get a chance to view the sample file I provided?  Wondering if there is any additional information that I should provide to help out with a resolution on this problem.

Thank you.