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 an...
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])
)
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

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

      I have checked around for the right way to create a date table but havent been able to find a definitive answer unfortunately.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        8 years ago

        hope this link will help to create date table