Forum Discussion

russell80's avatar
russell80
Helper III
5 years ago
Solved

Help With Event In Progress Visualization

I'm trying to create a stacked column chart which shows for each day over the last 28 days (or a period defined by a slicer) how many records were created, closed or in progress each day. The records...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    Howdy!

     

    Thank you for the apology.  ğŸ™‚

    I found this article that might interest you.  But I took a simpler approach to the problem.  Here's the code:

     

    Events Open in Period = 
    VAR EventsCreated =
        CALCULATETABLE(
            VALUES(ft_records[Record_ID]),
            FILTER(
                ALL(ft_records),
                ft_records[Start Date] <= MAX(Date_table[Date])
            )
        )
    VAR EventsPreviouslyClosed =
        CALCULATETABLE(
            VALUES(ft_records[Record_ID]),
            FILTER(
                ALL(ft_records),
                ft_records[Finish Date] < MIN(Date_table[Date])
            )
        )
    RETURN

    COUNTROWS(
        EXCEPT(
            EventsCreated,
            EventsPreviouslyClosed
        )
    )

     

    What we're doing here is creating a couple of table variables.  The first one is all events opened on or before the last date in a month.  The second is all events closed prior to the month in question.  I'm working with the assumption that an event will be open - even if only for an instant - if it happened to be closed on the same day.  The EXCEPT() function returns rows from one table that are not found in the other, and then we just count the rows.  Here's what the result looks like.

     

    I spot checked this in Excel by manually counting records for June and July and it appears accurate.  But you should probably check a little more just to be sure.

     

    You can download the PBIX here.

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

Ok...so I played with the PBIX you provided.  A couple things...

First, you need two relationships between the date table and ft_records, one on StartDate (primary I assume) and one on FinishDate.

Second...your measures seem unnecessarily complicated.  I created the following

Events Created = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        USERELATIONSHIP(Date_table[Date], ft_records[Start Date])
    )

Events Closed = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        NOT(ISBLANK(ft_records[Finish Date])),
        USERELATIONSHIP(Date_table[Date], ft_records[Finish Date])
    )

Events in Progress = 
    CALCULATE(
        DISTINCTCOUNT(ft_records[Record_ID]),
        ISBLANK(ft_records[Finish Date])
    )

Events Created Running Total = 
    CALCULATE(
        [Events Created],
        FILTER(
            ALL(Date_table),
            Date_table[Date] <= MAX(Date_table[Date])
        )
    )

Events Closed Running Total = 
    CALCULATE(
        [Events Closed],
        FILTER(
            ALL(Date_table),
            Date_table[Date] <= MAX(Date_table[Date])
        )
    )

Events in Progress Alternate = [Events Created Running Total] - [Events Closed Running Total]

The last one is clearly because I'm a huge fan of keeping it very simple.

 

This is a wall of numbers showing month by month each of the measures above...

 

Here's a combo clustered column/line showing events created vs closed as columns and those in progress (alternate/easy calculation) as the line.

I'd argue with you that your outstanding as an element in a stacked column hides the true meaning of what you're trying to project...events created, closed and outstanding.  Having it separately on the line calls out "here's what's left to be done".

You can download my revised PBIX here.