Forum Discussion

vmaru's avatar
vmaru
Frequent Visitor
4 years ago
Solved

Blank Calendar date in line chart

Hi,
I have a case table that has openeddate and closeddate columns. I want to show the count of ticket opened date and ticket closed date as values in a line chart and calendar date field in the axis. For Calendar date, I used the formula CALENDAR(Min([openeddate].[Date]), MAX([openeddate].[Date])) and mapped the date column with the openedate column. I am trying to use this calendar date in a line graph and it is blank. Not sure where I am going wrong in this. Hoping someone can help me resolve the issue.

Case Table:
number openeddate closeddate
C1001 10/17/2019 4:47:22 AM 10/17/2019 9:47:22 AM
C1002 10/17/2019 6:40:00 AM 10/18/2019 1:00:22 PM
C1003 10/19/2021 8:25:00 PM 10/21/2021 2:7:32 PM
C1004 11/17/2021 6:25:00 PM 11/26/2021 8:15:45 AM
C1005 11/25/2021 6:00:00 PM 11/26/2021 3:47:22 PM

Calendar Table:
Date Month Name Month Number Quarter Year
1/1/2019 January 1 1 2019

Expected Result:

Date Count of openeeddate Count of closeddate
10/17/2019 2 1
10/18/2019 1
.
.
11/25/2021 1

The expected result is to plot the table in a line chart.

 

Thanks

Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi vmaru ,

     

    Please create two "Date" type column in Power Query Editor and then create relationships among the "Date" type columns.

     

     

    Then create two measures like so:

    Count of openeddate = 
COUNT ( 'Case Table'[number] )

    Count of closeddate = 
CALCULATE (
    COUNT ( 'Case Table'[number] ),
    USERELATIONSHIP ( 'Calendar Table'[Date], 'Case Table'[closeddate - Copy] )
)

     

     

    Best Regards,

    Icey

     

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

1 Reply

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi vmaru ,

     

    Please create two "Date" type column in Power Query Editor and then create relationships among the "Date" type columns.

     

     

    Then create two measures like so:

    Count of openeddate = 
COUNT ( 'Case Table'[number] )

    Count of closeddate = 
CALCULATE (
    COUNT ( 'Case Table'[number] ),
    USERELATIONSHIP ( 'Calendar Table'[Date], 'Case Table'[closeddate - Copy] )
)

     

     

    Best Regards,

    Icey

     

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