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

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?

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      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 Ashish_Mathur

         

        Yes, that does the trick! Amazing.

         

        I was checking your steps and couldn't understand some of them, like, what's the purpose of the "order" table and some of the steps on the main table. If you ever have some free time would you mind explaning those?

         

        Either way thanks so much for your help! :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      "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" -- In the above message nothing opened/closed on same day for day 4.Its confusing.

       

      Not sure what count you are expecting ... Can you display the expected result for your original sample data in table format?

       

      Thanks

      Raj