Need to filter the same query 2 different ways, do I need 2 queries?

[Anonymous]

I have a log that has a created date and a closed date. I have 2 bar charts, log closed by month and log created by month. 

I want to limit each of these to the last 12 months for the relevant date, closed for the closed chart and created for the created chart. 

When I put a filter on it filters both charts by the date selected, either created or closed. That creates a problem for the closed log as it will filter out log items that were created more than 12 months ago even if they were not closed until the last 12 months.

Other than duplicating the query, what options do I have?

[Ashish_Mathur]

Hi,

See if my solution in the attached PBI file works.

[ray_aramburo · Accepted solution]

Hi @Anonymous , I suggest you these simple steps

1) Have/create a Calendar/Date table in case you don't have it

2) Create an active relationship between Date and Creaded Date

3) Create an inactive relationship between Date and Closed Date

4) Create 2 measures:

ValueForCreatedDate = CALCULATE([SumOrValueMeasure], USERELATIONSHIP('Calendar'[Date], 'ValuesTable'[CreatedDate]))

ValueForClosedDate = CALCULATE([SumOrValueMeasure], USERELATIONSHIP('Calendar'[Date], 'ValuesTable'[ClosedDate]))

5) Use the date from the Calendar table as the slicer and your measures as your values 🙂

[lbendlin]

Create a second, inactive relationship from the calendar date to the close date. Create a second measure with USERELATIONSHIP.  Then you can show both in the same date range.