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

  • RoofTopNomad's avatar
    RoofTopNomad
    Frequent Visitor
    3 years ago

    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's avatar
      MFelix
      Super User
      3 years ago

      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's avatar
        RoofTopNomad
        Frequent Visitor
        3 years ago

        Of course! What an obvious solution! Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!

    • 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