Forum Discussion

CraigC's avatar
CraigC
Icon for Helper I rankHelper I
8 years ago
Solved

Opened vs closed column chart with date filter

Hi All,

 

I am trying to create something like the below where i can compare how many incidents were opened per a day and how many incidents were closed per day. I have a column called Open Date and a Column Called Closed date. I do not have a date table.

Thank you

Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    create a measure for closed incident using userelationship function like below and use this on visual

     

    Closed Incidents = 
Calculate(Count(Table1[Incident]),
UseRelationship(Table1[CloseDate], Calendar[Date])
)

12 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I'd just create a date table and then relate your open date/closed date columns to it - as one of these relationships would be inactive you may need to stick a USERELATIONSHIP option in a measure to count whichever isn't going to be the primary relationship (assuming you just have the one data table)

    • CraigC's avatar
      CraigC
      Icon for Helper I rankHelper I
      8 years ago

      Hi Jthomson, thanks for the idea. Any help in creating the date table would be greatly appreciated. Would i need to constantly refresh the date table to a new date range?

      • jthomson's avatar
        jthomson
        Icon for Solution Sage rankSolution Sage
        8 years ago

        I mainly use custom tables as I want to include more than just a list of dates, but if you utilise CALENDARAUTO then it should be able to dynamically work out the earliest/latest dates based on your data set