cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aisnewwithdax Frequent Visitor
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 Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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.

View solution in original post

5 REPLIES 5
Super User
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.

View solution in original post

Super User
Super User

Re: Running total with dynamic date

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png

Aisnewwithdax Frequent Visitor
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!

Aisnewwithdax Frequent Visitor
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
Super User

Re: Running total with dynamic date

You are welcome.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 305 members 3,118 guests
Please welcome our newest community members: