cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering around the "Active" Value in a chart

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

201804201806
201804201807
201804201806
201804201806
201804201806
201804201806
201804201808
201804201809

 

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:

 

Picture 1.png

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!

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors