Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

counts rows for dates between

Hi All,

 

I need to count the number of closed tickets in a table between 2 dates. 

I have a date table connected to my data table

To get a closed date I had to use an if statement: 

Closed Date = IF(Query1[Date]<>"",Query1[Date],Query1[Date.1]) but the column is formatted as a date. 
I've tried a few measures to count closed tickets between 01/08/2018 to 
31/07/2019 but the output is wrong?
 
Help.. how can I do this?
 
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi  Anonymous ,

     

    To create a measure as below.

    Measure =
CALCULATE (
    COUNTROWS ( Query1 ),
    FILTER (
        Query1,
        Query1[Closed Date] >= DATE ( 2018, 08, 01 )
            && Query1[Closed Date] <= DATE ( 2019, 07, 31 )
    )
)

     

    If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

3 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Try this as a measure:

     

    Closed Tickets =
CALCULATE (
    COUNTROWS ( Query1 ),
    DATESBETWEEN (
        Calendar,
        DATE ( 2018, 08, 01 ),
        DATE ( 2019, 07, 31 )
    )
)

     

    You can, of course, make this a dynamic measure by replacing the hardcoded dates in the DATESBETWEEN function.

     

    Hope it helps.


    Best regards,

    Martyn

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi  Anonymous ,

     

    To create a measure as below.

    Measure =
CALCULATE (
    COUNTROWS ( Query1 ),
    FILTER (
        Query1,
        Query1[Closed Date] >= DATE ( 2018, 08, 01 )
            && Query1[Closed Date] <= DATE ( 2019, 07, 31 )
    )
)

     

    If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Worked perfectly, thanks