cancel
Showing results for
Did you mean:
Frequent Visitor

## Running total with dynamic date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Running total with dynamic date

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
5 REPLIES 5
Super User

## Re: Running total with dynamic date

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

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User

Hi,

Hope this helps.

Frequent Visitor

## Re: Running total with dynamic date

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!

Frequent Visitor

## Re: Running total with dynamic date

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

Super User

## Re: Running total with dynamic date

You are welcome.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 305 members 3,118 guests
Recent signins: