Help With Event In Progress Visualization

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.

I've put some thought into how to reply to this...

In your original request you stated "This has really got me stupmed and I'm not sure how to move forward from here, so any help would be welcome."  I am help.

In subsequent replies you stated:
"Thanks for taking the time to look at this, I've downloaded your pbix file and had a look at what you've done but it's not quite doing what I want." and "In your snippet I want to be able to select the point on the line chart for 45 in progress events and the record table on the left to filter and show just the 45. Similarly do the same for closed and created records. In your snippet it shows all the records and doesn't filter any further."  I asked for further clarification because you didn't provide a whole lot of context to work with so I was giving it my best guess.  I asked a couple of clarifying questions because what you're asking for doesn't make sense...see my last response.  And the most complete definition of context came in what came across as a lecture.  So not cool.

You need to realize that people like myself to respond in forums such as this are volunteering their time and effort for your help.  I'm sure you'll get this figured out.  Good luck!

Sorry for my response coming across as a lecture, it really wasn't my intention, so for that please accept my sincere apologies. I also appreciate that you don't need to spend your time helping strangers on the internet, for that I'm also very grateful.

If I'm honest, I'm not sure I'll figure this out any time soon. It's only in the last few weeks I've started using Power BI and have been learning about data models, DAX, queries etc... and the learning curve has been very steep (the Guy In A Cube series on YouTube has been pretty helpful!). However, if I do find a solution to this and I remember this post, I'll make sure to reply so hopefully it's of help to others.

Thanks again for all your help!

Provide a sample PBIX showing what you've got so far?

Thanks for taking the time to look at this, I've downloaded your pbix file and had a look at what you've done but it's not quite doing what I want.

I've added a table visual to the report page you created which lists all the records. What I want to happen is when I select either of the columns or the line, the records associated with that value are filtered and shown on the table. What happens at the moment is I'm not sure but it looks like the records are filtered only for the month selected on the chart and not the measure. Is there a way I can get the records in the table to filter e.g. when I select the closed events column in November, the table shows all the records which were closed in November and just those records?

PBIX Report with record table: Download 

In your snippet I want to be able to select the point on the line chart for 45 in progress events and the record table on the left to filter and show just the 45. Similarly do the same for closed and created records. In your snippet it shows all the records and doesn't filter any further.

Here's the correct link: Download 

This may not be the ideal solution but it does what I need and I'm pretty chuffed I managed to figure this out 👍

I re-read the article I posted in the first post and it talked about creating a table first to help do with the calculations, so that's what I tried. I created 3 tables using DAX, one for "Created" records, one for "Open" and one for "Closed" and a 4th table (record history) to Union them together. By adding additional columns to the tables to capture the record state and a value for summing in the visual, I got something that works. I had to duplicate the record table so I could create a relationship between it at the "record history" table, so when I clicked on the visual I could get the records associated with the selection (not sure if this has any impact).

You can download the pbix file here: Download 

Looks like we were posting at the same time 😁

I'll take a look at your post later tonight once I get a chance to read it 👍

Hi russell80 ,

So your issue is solved ,right?

If so,could you pls mark the reply as answered to let more people find the solution?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!