Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Creating a line graph with current active tickets

Hi there

I love power bi but for this time a struggling how to solve the next problem. I have a table where I have starting date and an end date where problem is fixed. 

I want to create a line graph based on dates where I can see how many ticket are active on any current date as they fall between start and end date or are still active as there is no end. Can anybody help me out

 

For the moment I did not connect the table to a date table

 

Best regards

Eric 

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You don't need a relationship with the date table, just use

    Active cases =
VAR RefDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Start date] <= RefDate
            && (
                ISBLANK ( 'Table'[End date] )
                    || 'Table'[End date] > RefDate
            )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I can jump through hoops, it works perfectly

    Thanks very much, I can now go to bed and have a peacefull night