Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
joshcomputer1
Helper V
Helper V

Count between start and end date

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

AbbyTuesday, January 1, 2019Thursday, January 3, 2019
BillWednesday, January 2, 2019Wednesday, January 2, 2019
ChrisThursday, January 3, 2019Saturday, January 5, 2019
DaveThursday, January 3, 2019Friday, January 4, 2019
ElliotFriday, January 4, 2019Wednesday, January 9, 2019
FrankSaturday, January 5, 2019Monday, January 7, 2019

 

DESIRED RESULT SET

DateEmployee Count
1/1/20191
1/2/20192
1/3/20193
1/4/20193
1/5/20193
1/6/20192
1/7/20192
1/8/20191
1/9/20191
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

Start_end_date.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
MFelix
Super User
Super User

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:

Start_end_date.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi, @MFelix 

I've tried this solution when having csv of excel files as a dataset which seems to work fine. However when I'm working with an online database using direct query it doesn't. I'm trying to count the number of id's between the start date and end date, but with direct query it's only counting the id on the start date or not whatsoever. 

Any ideas why this method doesn't work while working with online datasets?

I appreciate any input!

Hi @Anonymous ,

 

If you are using direct query then the calculation needs to be different since you are not abble to create disconnected table.

 

Try the following code:

 

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Felix, I'm facing a similar challenge on this subject and came across this post.

 

I'm trying to use a direct query model to report historical monthly count of individuals between their respective start and end dates. For example, in February, an individual with Start_Date Jan 20 and End_Date Mar 20 counts as 1. 

 

Fact_Table:

Person_ID | Start_Date | End_Date

 

Date_Table:

Date Dt | Date_ID | Many other columns

Trying to build the measure that you included above doesn't allow me to reference the Dim_Date table in the filter section. It allows me to pull Start and End date into the calculate statement, but not the Date Dt column from the Date Table. Anonymous seemed to have a similar issue. Do you have an idea on how to implement the Date >= Start Date and Date <= End Date? 

Hi @Paws ,

 

Do you have any relationship between the tables? What is the syntax you are using?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix ,

thanks for your quick response. Not sure what you mean though. With the COUNTROWS statement I have to refer to a table and not a column and I also can't refer to the date table in this measure. 

Hope you can help me

Cheers,
Marco

Hi @Anonymous ,

 

You need to reference the table that you want to filter, the column is reference on the filter statement.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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..

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Of course! What an obvious solution! Thanks

Anonymous
Not applicable

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.