cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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, Date_Closed_UTC

 

I have a separate Calendar table marked as the date table.

 

I have created an active relationship between Calendar[Date] and ServiceData[Date_Opened_UTC]

I have created a passive relationship between Calendar[Date] and ServiceData[Date_Closed_UTC]

 

All date fields are set to type: Date

 

To get my counts, I created two measures as follows:

Measure TicketsClosed = CALCULATE(count(ServiceData[TicketNbr]),USERELATIONSHIP(ServiceData[Date_Closed_UTC],'Calendar'[Date]))
Measure TicketsOpened = count(ServiceData[TicketNbr])
 
If I create a Table visual, pull in the Calendar[date] field, then set to not use heirarchy it shows me each day in the calendar range, as such:
TableViewWithoutMeasure.png
 If I then add the Measure TicketsClosed and Measure TicketsOpened, I was hoping to see the number of tickets opened and closed each day but instead it removes all the dates and just shows me a count of the TicketNbr as shown:
TableViewWithMeasure.png
 
I suspect that the relationship is not being used/observed. I HAVE successfully gotten this working before with another data table. I'm not sure why this one would be any different!
 
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculating date opened and date closed in same report

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.

tab1.png

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

tab2.pngtab3.png

 

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.

View solution in original post

7 REPLIES 7
Highlighted
Community Champion
Community Champion

Re: Calculating date opened and date closed in same report

 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?

Highlighted
Helper III
Helper III

Re: Calculating date opened and date closed in same report

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

DateType.png

DateField.png

 

Date_Closed_UTC is a Date field as well:

DateClosed.png

 

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?

Highlighted
Community Champion
Community Champion

Re: Calculating date opened and date closed in same report

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.

 

Annotation 2020-06-13.png

Highlighted
Helper III
Helper III

Re: Calculating date opened and date closed in same report

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

 

WithMeasure.png

 

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

Highlighted
Community Champion
Community Champion

Re: Calculating date opened and date closed in same report

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

 

Annotation 2020-06-13a.png

Highlighted
Super User IV
Super User IV

Re: Calculating date opened and date closed in same report

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Microsoft
Microsoft

Re: Calculating date opened and date closed in same report

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.

tab1.png

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

tab2.pngtab3.png

 

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors