Running total with dynamic date

[Anonymous]

Hello

I've been struggling with this problem now for couple days.

I've got 2 tables. Data and calendar. Here's an example of data table (it has only 3 columns)

ID         Creation date        closing date
1          1/1/2000                5/1/2000
2          2/1/2000                6/1/2000

3          2/1/2000                6/1/2000

4         3/1/2000                  13/1/2000

5         10/1/2000                14/1/2000

and so on. I have data for about 2 years and now I would like to create a measure that could show day to day the amount of ID's (count) that have been open for 2 or more days. I'd like to calculate so that creation day is 0 and the closing day doesn't count anymore.

So the result matrix would look like this: (date from date table)
Date            count

1/1/2000          0

2/1/2000         0
3/1/2000         1     (ID 1)

4/1/2000         3     (ID 1,2,3)

5/1/2000         3     (ID 2,3,4)

6/1/2000        1    (ID 4)

And yes 0 is better value than blank for me, and all in all this measure would be used to calculate the percentage of >=2 days open/all open.

The problem has been with using measures and I haven't been able to do like a for-loop which would go day by day that calendar and do the needed checks for all the ID's

Thanks if anyone could help me 🙂

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks for your answer! This was almost the same solution I managed to do yesterday after a long struggling.

[Ashish_Mathur]

You are welcome.

[MattAllington · Accepted solution]

This is called “events in progress”. https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

[Anonymous]

Wow. That's such a clear explanation of the problem! I managed to find almost the same way for this problem myself, but this explanation really opened my eyes about what was the problem with I was struggling. Thanks!