Forum Discussion

Dasbas's avatar
Dasbas
Frequent Visitor
9 years ago
Solved

How to count values at a certain date

Hello,

 

I am trying to create a dashboard for a service call system. In this system I have a Call date (open date) and a Close date. What i want to do is create a line graph which shows at what time how many calls were open.

 

I thought I could solve this by creating a Column which counts the rows where the Close date is smaller than the open date. This however doesn't work since it will just count all the rows since for one row the close date is never before the open date.

 

Is there a way to store the open date for one row and then count how many rows have a close date before said open date?

 

Thank you!

Need Help
  • LaurentCouartou's avatar
    LaurentCouartou
    9 years ago

    I forgot to mention a date should only appear once in your Dates table.

     

    Regarding the filter expression, I made a copy and paste error.

     

    [Number of calls opened at] = CALCULATE( COUNTROWS( Calls )
      , FILTER( VALUES( Calls[opendate] )
                ,  [opendate]  < MAX( Dates[date] )
      )
      , FILTER( VALUES( Calls[closedate] )
                ,  [closedate]  >= MAX( Dates[date] )
      )
)

     

4 Replies

  • LaurentCouartou's avatar
    LaurentCouartou
    Icon for Solution Supplier rankSolution Supplier
    9 years ago

    I understand you want to calculate the number of calls that were open before a given date and have been closed on or after this date.

     

    Adding a Dates table to your model, and then filtering on it will make things easier. Following best practise, your Dates table should contain contiguous dates.

     

    The measure would look like:

    [Number of calls opened at] = CALCULATE( COUNTROWS( Calls )
      , FILTER( VALUES( Calls[opendate] )
                ,  [opendate]  < MAX( Dates[date] )
      )
      , FILTER( VALUES( Calls[closedate] )
                ,  [opendate]  >= MAX( Dates[date] )
      )
)

    MAX( Dates[date] ) implies the results shown, if you select more than one date, will be for the last date in the selected period.

    • Dasbas's avatar
      Dasbas
      Frequent Visitor
      9 years ago

      Hi Laurent,

       

      Thank you for you answer.

      Tyring to implement your advice as we speak, however this gives me an issue: 

      [opendate]  < MAX( Dates[date] )

      [opendate] refers to a column and not a single value. Do you have any idea how to fix this? 

       

      I also can't create a releationship between the dates table and the call date due to the lacking of unique values?

      • LaurentCouartou's avatar
        LaurentCouartou
        Icon for Solution Supplier rankSolution Supplier
        9 years ago

        I forgot to mention a date should only appear once in your Dates table.

         

        Regarding the filter expression, I made a copy and paste error.

         

        [Number of calls opened at] = CALCULATE( COUNTROWS( Calls )
      , FILTER( VALUES( Calls[opendate] )
                ,  [opendate]  < MAX( Dates[date] )
      )
      , FILTER( VALUES( Calls[closedate] )
                ,  [closedate]  >= MAX( Dates[date] )
      )
)