Count between start and end date

[joshcomputer1]

I have a table as follows.  I want to get a count of names between start and end date.  the result is a table like figure 2.  I have a datetable related to this table. When I do the measure, it is just giving me a count based on start date.  So, if I have two that started on 1.3 then it shows a 2. Which is not what I want. 

The Measure I tried is this:

TEST NEW Headcount = calculate(

                                           count(TEST[Name]),

                                           Filter (

                                           TEST,

                                          TEST[Start_Date] <= calculate(max(datetable1[date]) ) &&

                                          TEST[End_Date] >= calculate(min(DateTable1[Date]))))

Name              Start_Date                                       End_Date

Abby	Tuesday, January 1, 2019	Thursday, January 3, 2019
Bill	Wednesday, January 2, 2019	Wednesday, January 2, 2019
Chris	Thursday, January 3, 2019	Saturday, January 5, 2019
Dave	Thursday, January 3, 2019	Friday, January 4, 2019
Elliot	Friday, January 4, 2019	Wednesday, January 9, 2019
Frank	Saturday, January 5, 2019	Monday, January 7, 2019

DESIRED RESULT SET

Date	Employee Count
1/1/2019	1
1/2/2019	2
1/3/2019	3
1/4/2019	3
1/5/2019	3
1/6/2019	2
1/7/2019	2
1/8/2019	1
1/9/2019	1

[MFelix · Accepted solution]

Hi @joshcomputer1 ,

Your measure is correct however your datetable needs to be disconnected from your Test Table.

Since you have a relationship on the Start Date your data is filtered by the start date only so give you 6  as a result and not the expect outcome.

Check the image below and PBIX file attach:

Regards,

MFelix

[RoofTopNomad]

Hi @MFelix ,

I know this is a few years old but super helpful. Thank you.

Using your example I was able to make it for my use case. However, if I put the data in a bar chart with calendar as X and the measure as Y it and a slicer with the calendar it works great. However, since it's a discconnected table I am not able to click on the chart to filter a table. Is that possible? 

for example:

I'm tracking staff on trips with a start and end date. Each trip has a unique ID. I want to be able to see on a chart the number of trips that are occuring at any given month and when i click that month it will filter my table to show me who is traveling. Your example shows the correct data on the chart but it doesn't filter back the table. Wondering how one would tackle that with it being disconnected. 

Thanks for taking the time to answer a question from 2019..

[MFelix]

Hi @RoofTopNomad ,

In this case just try to add the metric on the table visualization filters and set the filter to is not blank. This should give you the expected result that is filtering the records based on the metric selection.

[RoofTopNomad]

Of course! What an obvious solution! Thanks

[Anonymous]

here is my suggestion to tackle this :

first Make a new date table.

DateTable = Calendar(Minx(table1,Table1[Date]),Now()) 

Now add a new calculated column:

DateSlicer = CALCULATE(
    COUNTROWS(contractid),
    FILTER(Table1,
        DateTable[Date] >= [startdate] &&
        DateTable[Date] <= [Enddate])

Hope this  will help.