Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count values from date

Hello guys,     I have a data set here that's like this:   ID Opening Data Closing Date 1 03/06/2018 03/06/2018 2 03/06/2018 04/06/2018 3 04/06/2018 05/06/2018 4 05/06/20...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    I believe this is the result you want.  You may download my PBI file from here.  I edited the closing date for ID 9 to June 12.

     

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous

 

How do you calculate the count? Start/ end should be same? For the given above sample data, what is the expected coiunt for each start date?

 

Thanks

Raj

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello Anonymous!

     

    I need to count the number of IDs for each date. Each ID represent the date when a ticket was open and closed, so my goal is to have them a graph illustrating how many tickets were open and closed on each day.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      If an ID opened on Jul 1, 2018 and closed on Jul 5, 2018, then i understand the opening and closing dates.  But what about the dates in between?  How should the dates from Jul 2-4 be treated?

       

      Show your expected result in a Table.  Once the table is ready, one can simply switch the result to a graph.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hello Ashish_Mathur

         

        The dates in between should be ignored. My expected result is to get to know how many tickets were opened in a day, and how many were closed in that same day.

         

        So an example:

        IDOpening DateClosing Date
        103/06/201803/06/2018
        203/06/201804/06/2018

         

        I would want to have a graph showing that I had 2 tickets opened on day 3, and only one closed on that same day, aswell as 0 tickets opened on day 4, and one closed on that same day.

         

         

        I did a CALCULATE(COUNT(Table1[Opening Date]);Table1[Opening Date]) and a CALCULATE(COUNT(Table1[Closing Date]);Table1[Closing Date]) to count the number of occurences for each date, but when I try to plot the graph, I get mismatched results.

         

        I believe it's a relationship problem. I have a separate date table, and if I relate it to the opening date, I get correct results on the opening date, but wrong results on the closing date. If I relate it to the closing date, I get correct results for the closing date, but wrong results for the opening date.

         

        Any ideas?