Forum Discussion

joshcomputer1's avatar
joshcomputer1
Helper V
7 years ago
Solved

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 ju...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

MFelix's avatar
MFelix
Super User
2 years ago

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])
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi Anonymous ,

     

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