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

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

        Ok so i have a date table with both my opened incidents and closed incidents linked to the date column. The closed incident column being an inactive link with the opened incidents being the active link. If i create the graph now it will display the same info for the opened and closed incidents. How do i go about linking the closed incidents column now to show the right data?