Forum Discussion

Shpongle's avatar
Shpongle
Icon for Helper III rankHelper III
6 years ago
Solved

Calculating date opened and date closed in same report

I know, I know! This question has been asked a million times 😁   The problem is, I can not figure it out.   I have a ServiceData table with the following fields: TicketNbr, Date_Opened_UTC, Dat...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Shpongle ,

    Based on my test, it is indeed related to the data type. The data type of the date in calendar table and fact table should be the same from the beginning.

    For example, when your calendar table is date type, your fact table is date/time type but you have changed them to date already, it will show the result just like your latest reply.

    If your fact table and calendar table are date types or date/time types initially, it will work fine.

     

    My test sample file is attached that you can refer, hopes to help you understand it: Calculating date opened and date closed in same report.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

 Is the Date field really a column or maybe a measure? In which case you want to say "Don't summarize"

 

Also - is your Date_Opened_UTC a DateTime field or a Date field?  In which case why call it DateOpenedUTC?

Shpongle's avatar
Shpongle
Icon for Helper III rankHelper III
6 years ago

The date field is a column, set as date type, no summary:

 

Date_Closed_UTC is a Date field as well:

 

I should mention - the source data is date/time but I changed all to date after importing into PowerBI... Could that have something to do with that?

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Potentially. Rather than changing it I would add another column in Power Query with just the date value.

     

    Your measure formula works fine for me.  However you need to enable "Show items with no data" for scenarios where on a particular day no tickets were opened.  If you don't do that then the closed tickets won't show up either.

     

    • Shpongle's avatar
      Shpongle
      Icon for Helper III rankHelper III
      6 years ago

      OK - now I can see that the ticket counts are not being associated with any date!

       

       

      A little bit of progress, but now what do I do?? 🤔

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        check the table relationships again, make sure the right fields are selected. It works for me.

         

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Shpongle ,

    Based on my test, it is indeed related to the data type. The data type of the date in calendar table and fact table should be the same from the beginning.

    For example, when your calendar table is date type, your fact table is date/time type but you have changed them to date already, it will show the result just like your latest reply.

    If your fact table and calendar table are date types or date/time types initially, it will work fine.

     

    My test sample file is attached that you can refer, hopes to help you understand it: Calculating date opened and date closed in same report.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.