Produce daily counts out of period ranges

[rdaudt2021]

Hi.

This is about observing and counting objects in a given space (physical or logical, it doesn't matter). Objects will enter a space in a given day and stay there for any number of days. What we want to know is how many objects are in that space at any given day. We want this object count over time, in a period of a month or so.

The end result should be be something such as the chart below.

The data model that I will be given is shown below

I can't wrap my mind around what the count measure should be so that the visual above can be produced out of the Objects fact. Looking for any help in this regard.

A few other details:

- there is only one "space" where objects can get in

- the same object will not enter the space more than once

- users should be able to slice-and/dice the visual by the three dimensions shown above: date, color and size.

Thank you very much.

Roque Daudt

[TomMartens · Accepted solution]

Hey @rdaudt2021 ,

the challenge you are facing has a name: event-in-progress.

This article references all the important articles: https://blog.gbrueckl.at/events-in-progress/

I recommend starting with the article from @SqlJason 

If you get stuck, create a pbix file that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link (here in this thread). If you are using Excel to create the sample data, share the xlsx as well.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[rdaudt2021]

That's fantastic, Tom. Exactly what I needed. I may come back to it in regards to a few details but now I know the approach. Thank you very much.