Using a date range selected from a slicer based on one date column to filter another date column?

[stevenmcginnis]

Hello Power BI Community,

I would appreciate your help is trying to solve this problem. 

Here's the situation: I have two cards on my dashboard, 'Created Requests' and 'Delivered Requests.' The 'Created Requests' card should display the count of requests created in the selected year. The 'Delivered Requests' card should show the total count of requests closed in the selected year, ideally with a breakdown of those created in the same year and those carried over from previous years (backlog).

To complicate matters, the date slicer being used is linked to the created date column and must remain that way as this is how the data should be viewed for the rest of the visuals.

To provide context and allow you to reproduce the problem, I have attached a table with some sample data at the bottom of this post.

Using this data, when you select the year 2023 in the slicer, the 'Created Requests' card should display a count of 8 requests. But, calculating the figures for the 'Delivered Requests' card is where I am running into issues. It should show the total count of requests closed in 2023 (6), along with the backlog count 4 (TCKT004, TCKT006, TCKT007, and TCKT012) comprising requests closed in the selected year but created in a previous year.

I would appreciate your valuable insights, suggestions, and any sample DAX measures that can help me overcome this hurdle and correctly calculate the counts for the 'Delivered Requests' card while maintaining the link between the slicer and the created date column for other visuals on the page.

Any support would be greatly appreciated, and thank you in advance!

TicketNumber	EndUser	Team	CreatedDate	Status	ResolutionDate
TCKT001	User1	TeamA	10/01/2022	Done	15/01/2022
TCKT002	User2	TeamB	20/02/2022	Done	25/02/2022
TCKT003	User3	TeamA	15/03/2022	Done	18/03/2022
TCKT004	User4	TeamC	05/04/2022	Done	10/04/2023
TCKT005	User5	TeamB	20/05/2022	Done	25/05/2022
TCKT006	User6	TeamA	15/06/2022	Done	18/01/2023
TCKT007	User7	TeamB	05/07/2022	Done	10/07/2023
TCKT008	User8	TeamC	20/08/2022	Done	25/08/2022
TCKT009	User9	TeamA	15/09/2022	Done	18/09/2022
TCKT010	User10	TeamB	05/10/2022	Done	10/10/2022
TCKT011	User11	TeamC	20/11/2022	Active
TCKT012	User12	TeamA	15/12/2022	Done	18/02/2023
TCKT013	User13	TeamB	05/01/2023	Active
TCKT014	User14	TeamC	20/02/2023	Done	25/02/2023
TCKT015	User15	TeamA	15/03/2023	Done	18/03/2023
TCKT016	User16	TeamB	05/04/2023	Active
TCKT017	User17	TeamC	20/05/2023	Done	25/05/2023
TCKT018	User18	TeamA	15/06/2023	Done	18/06/2023
TCKT019	User19	TeamB	05/07/2023	Done	10/07/2023
TCKT020	User20	TeamC	20/08/2023	Done	25/08/2023

[Anonymous]

Hi @stevenmcginnis ,

Please refer to my pbix file to see if it helps you.

Create a year table with the years in the table.

Then create 2 measures.

Created Requests =
VAR _selectedvalue =
    SELECTEDVALUE ( 'Table 2'[yearcreated] )
VAR _created =
    CALCULATE (
        COUNT ( 'Table'[TicketNumber] ),
        FILTER ( ALL ( 'Table' ), 'Table'[yearcreated] = _selectedvalue )
    )
RETURN
    _created

Delivered Requests =
VAR _selectedvalue =
    SELECTEDVALUE ( 'Table 2'[yearcreated] )
VAR _done =
    CALCULATE (
        COUNT ( 'Table'[TicketNumber] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CreatedDate] >= _selectedvalue - 1
                && 'Table'[Status] = "Done"
                && 'Table'[yearsoultion] = _selectedvalue
        )
    )
RETURN
    _done

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[stevenmcginnis]

@Anonymous, 

Thank you for your response and for dedicating time to create a sample Power BI file addressing the issue. Regrettably, the current solution does not work because the report's hierarchy is based on month and year. I apologize for not providing a clear explanation initially, but I have now made edits to the pbix file to better illustrate the problem, a link to the One Drive is available here: Using a date range selected (edited) . The slicer in the file now replicates the slicer in the report I am working with. This slicer is necessary to filter other visuals on the page, including the one shown in the file. I have also displayed all the visuals that should be responsive.

It is crucial that all visuals are filtered by the "CreatedDate" field, while only the "Delivered Requests" card should use the "ResolutionDate" field (although this may be a bit complicated). I encountered some issues because the hierarchy for the "CreatedDate" slicer is limited to month and year, causing a DAX variable created with SelectedValue to return a range that always appears blank. I did consider using a Date Table, but I believe (correct me if I'm mistaken) that it would return all values for both "CreatedDate" and "ResolutionDate," which is not desirable.

The desired outcome is to maintain the current report layout, but have the cards display the number of tickets created within the selected date range (Created Requests) and the number which have been resolved in the selected date range regardless of when they were created (Delivered Requests). I hope this explanation is clear enough, and I'm happy to provide further clarification if needed.

Once again, I greatly appreciate your support.

Regards,

Steven

Using a date range selected (edited) 

[Anonymous]

Hi @stevenmcginnis ,

Do you want the output to be filtered based on year and month? And if the field is createdate? Because it also filters other visual objects on the same page? I think you need to create a new table and put the year and month fields of the new table in the filter to filter the results you want for both cards. If the year and month is filtered, does it give different results than what you mentioned in your post? Does it change based on the month?

Best Regards
Community Support Team _ Rongtie

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

[stevenmcginnis]

@Anonymous ,

Thanks again for your swift response, I have responded to your questions inline below

1. Do you want the output to be filtered based on year and month?

   • Yes, that is correct. I want the output to be filtered on both month and year, ideally like the slicer in the pbix file I linked.

2. And if the field is createdate?

   • For all visuals except the 'Delivered Requests' card, the filter should be the 'createdate' field. However, for the 'Delivered Requests' card, the filter should be the 'resolutiondate' as we want to capture all tickets that were resolved within a given timeframe and not created.

3. Because it also filters other visual objects on the same page?

   • Yes, the slicer should filter all visuals on the page.

4. I think you need to create a new table and put the year and month fields of the new table in the filter to filter the results you want for both cards.

   • I have tried this, but I am getting an error when I try to create a monthcreated column to capture the month like you have in your sample file. Is there a better way to capture the month?

5. If the year and month are filtered, does it give different results than what you mentioned in your post? Does it change based on the month?

   • Apologies, but I am not too sure what you are asking. The data should change based on the month, but in the sample file you kindly generated, there is no month filter, and I am unable to capture a single month as I am encountering the following error. "A table of multiple values was supplied where a single value was expected"

Thank you for your continued support.

Regards,

Steven