Use of Data Table for creating a graph with 2 date columns from the data source

[MadhavDholakia]

Hello,

I am working with ITSM Tool Data which contains multiple columns including TicketID, CreatedDate, ClosedDate, TicketType etc. Data includes any tickets last updated in the last 2 years from today (i.e., 3rd Jun)

• File -> Options & Settings -> Options -> Current File -> Data Load

• Both CreatedDate and ClosedDate are in DateTime format.
• A Date Table is created as given below and is marked as a date table:

Date = CALENDARAUTO(3)

• No Relationship between these 2 tables defined:

• Measure CreatedCount:

CreatedCount =

    CALCULATE(

        COUNTROWS('FSTickets'),

        FILTER(

            'FSTickets',

            'FSTickets'[Created Date] >= MIN('Date'[Date]) &&

            'FSTickets'[Created Date] <= MAX('Date'[Date])

        )

    )

• Measure ClosedCount:

ClosedCount =

    CALCULATE(

        COUNTROWS('FSTickets'),

        FILTER(

            'FSTickets',

            'FSTickets'[Closed Date] >= MIN('Date'[Date]) &&

            'FSTickets'[Closed Date] <= MAX('Date'[Date])

        )

    )

• A slicer has been added (Field Date from Date Table) - Date Range: 01/05/2024 to 31/05/2024

•  Added 2 cards for CreatedCount and ClosedCount measures, but the values displayed are not correct.
• If I add below relationship, both cards show blank values: 

Date [Date] - FSTickets [Created Date]: one to many / active

Date [Date] - FSTickets [Closed Date]: one to many / inactive

This is my first hands-on experience with Power BI - can someone please suggest what I am missing here and how can I achieve correct Closed vs Created Ticket Count based on the date range selected in the Slider?

Thank you.

Regards,

Madhav 

[Anonymous]

Hi @MadhavDholakia 

Thanks for the reply from @miTutorials.

@MadhavDholakia ,when selecting 01/05/2024 - 31/05/2024 in the slicer, what should be the correct result you expect?

And I noticed that in the PBIX data you provided, the Closed Date is blank. So I want to make sure with you if there is a problem with this column of data.

Best Regards,
Yulia Xu

[MadhavDholakia]

Hi @Anonymous - Thank you for your reply.

Tickets which are not yet closed will have Closed Date as null.

There are 6438 tickets last updated in May 2024 (Last Updated Date).

Total Tickets Created in May 2024 (Created Date) are 5785.

Total Tickets Closed in May 2024 (Closed Date) are 5169.

These are the numbers I am expecting when slider is between 01-05-2024 and 31-05-2024.

Closed Count Measure: 

ClosedCount = CALCULATE(COUNT(TicketData[Closed Date]))

Created Count Measure: 

CreatedCount = CALCULATE(COUNT(TicketData[Created Date]),USERELATIONSHIP(TicketData[Created Date],'Calendar'[Date]))

Thank you.

[miTutorials]

Check out this tutorial , hope this helps!

Dealing with Multiple Date Fields to bring values from same time period in Power BI | MiTutorials (y...

[MadhavDholakia]

thanks @miTutorials - this was helpful.

I have followed the steps as per tutorial video. When I am creating both the measures for CreatedCount and ClosedCount, it seems I am missing something.

Unlike Sales Value in Tutorial Example, I have a column Numeric Ticket ID - so I cannot use CALCULATE(SUM(..)..)

Measures I have created are given below and surely they are not correct as I am not getting expected results:

• ClosedCount = CALCULATE(COUNT(FSTickets[ID Numeric]))

• CreatedCount = CALCULATE(COUNT(FSTickets[ID Numeric]),USERELATIONSHIP(FSTickets[Created Date],'Calendar'[Date]))

Can you please suggest what is wrong here? Thank you.

[miTutorials]

Can you share sample pbix to help please ?

[MadhavDholakia]

Hi, I am not seeing an option to attach a file here - am I missing something?

Edit: because I have just joined this forum and do not have sufficient points/rights - please see this WeTransfer Link for sample pbix file.

https://we.tl/t-YujU7mVtUY

Thank you.

[MadhavDholakia]

Hi @miTutorials - sorry but can you please suggest what I am missing here? Thank you.

[miTutorials · Accepted solution]

You are trying to create a relationship with a field that has DateTime - You will have to create a Date Column, create relationship and then use the measure.

Just bring in the Date from Calendar and Closed Date from Ticket table to understand what I'm talking about.

Hope that helps!

Also attaching the Pbix here.

[MadhavDholakia]

thanks @miTutorials - this is now working as expected. Thank you.