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 ti...
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] )
      )
)

     

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
Solution 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] )
      )
)

 

  • Dasbas's avatar
    Dasbas
    Frequent Visitor
    9 years ago

    Thank you!