How to create a record selection date slicer

[Anonymous]

In my imaginary database, I have a single table, "Mutant", containing the following data. I want to allow the user to select a date from, say, the last five years, and have my card count how many of my Mutants were alive on that date.

The date format is yyyy-mm-dd here, and <blank> in the DateOfDeath implies the Mutant is still alive.

MutantID	DateOfBirth	DateOfDeath
1	2022-05-01	2022-06-01
2	2022-05-13	2022-06-20
3	2022-05-31	<blank>
4	2022-07-01	<blank>

Suppose the user selects 2022-06-10. The answer should be 2, because we'll be counting rows 2 and 3, but not rows 1 or 4.

I can't figure out how to create the control that allows the user to select the date, or then how to use the selected date to drive what gets included and what gets excluded.

I am such a noob. Is anyone able to assist?

[Anonymous]

So sorry. I can't make any sense of that. I'm used to tools like Visual Studio, SQL Server or even Access, where you just put a control on the screen, read its value, then have an expression in a WHERE clause something like (start<selecteddate and end>selecteddate) to determine whether a row gets included or not. I'm afraid I am lost looking at your blog post. It goes wrong for me here: "Also, we generated a Date table."

eRRR, you generated a what? What even is a date table? I've tried to make sense of date tables, and I think I understand there is something called a built-in date dimension and there are custom date dimensions, and these have something to do with putting dates into months, quarters and so on, but I have no idea how any of that is relevant to what I'm trying to do.

I'm new to Power BI. Is it always like this? Like, in order to figure out whether a date lies within a range, you have to build a "date table", need a "date calendar" that is marked as a date in model view, and have "Start Date joined with Date of Date Dimension and Active, in addition to Inactive relation Termination Date and Date." - I don't even understand what most of that means! 

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please create a separate date table, like this.

Calendar = 
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) )

Then create the measure and filter.

Measure = 
COUNTROWS (
    FILTER (
        'Table',
        'Table'[DateOfBirth] <= SELECTEDVALUE ( 'Calendar'[Date] )
            && (
                'Table'[DateOfDeath] > SELECTEDVALUE ( 'Calendar'[Date] )
                    || 'Table'[DateOfDeath] = BLANK ()
            )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks! That's easier to follow, but I do still have a few difficulties with it, if I may?

The first is that when I try to drop the new measure into the filters area for the page, it doesn't work. When I let go of the mouse-drag, it just bounces back without adding to the filters. 

If I add it to the filters for a visual, that works, but I'm unable to change what appears under "Show items when the value". The default is "is less than", and when I click the drop-down, it's unresponsive. 

Everything else on the page appears to be working fine. 

The second is to ask how I can let the user type in the date? I have more than ten years' worth of dates, so presenting a scrollable list isn't really going to work.

Looking ahead, I can see I am going to get into difficulties when I come to use summarisation. The approach you've kindly recommended will be fine for unsummarised tables, but as soon as there is summarisation, the filter won't select only the records with a date range that spans the selection. It will select an entire group of records if any one record in that group has a date range spanning the selection. This is because as soon as the count for a cell is not blank (say, it's 1), all the records matching the row and column headers will be counted, not just the one record that has a spanning date range.

I am running Power BI Desktop Version: 2.108.603.0 64-bit (August 2022)

Thanks for much for your help. This is surprisingly complicated.

[amitchandak]

@Anonymous , Seem like the same as HR problem, I discussed in blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970