Forum Discussion

russell80's avatar
russell80
Icon for Helper III rankHelper 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.

russell80's avatar
russell80
Icon for Helper III rankHelper III
5 years ago

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!

 

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
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.