Forum Discussion
DAX Calculating Open Project Trends
Hello Community -
I am looking to calculate Open Project Counts and the age buckets that these projects are past due. Here is a small sample set of data with just four of the key data points:
| Project ID | Actual Start Date | Actual Complete Date | Status |
| 1 | 5/31/2023 | 7/1/2023 | C |
| 2 | 6/5/2023 | 11/1/2023 | C |
| 3 | 6/8/2023 | 10/30/2023 | C |
| 4 | 7/10/2023 | 10/31/2023 | C |
| 5 | 8/1/2023 | 12/15/2023 | C |
| 6 | 9/9/2023 | O | |
| 7 | 9/30/2023 | O | |
| 8 | 10/31/2023 | 12/1/2023 | C |
| 9 | 12/1/2023 | 12/13/2023 | C |
| 10 | 12/13/2023 | O |
Calculating the Open Projects and age is simple for an As Of Now view, but I need to be able to see those metrics and I need to see them for the prior 6 weeks (1 week ago today, 2 weeks ago today.......6 weeks ago today).
As of today 12/19/23 I would expect to see 3 Open Projects (1 in the 0-30 days past due, 1 in 31-90 Days past due and 1 in 91+ Days).
So if I looked back 1 week prior (12/12/23….TODAY-7 days) I want to see how many open projects there were and what buckets they were in.....I would expect 5 open projects (project No.s 5-9) based on the start dates and completion dates.
| Project ID | Actual Start Date | Actual Complete Date | Age |
| 5 | 8/1/2023 | 12/15/2023 | 133 |
| 6 | 9/9/2023 | 94 | |
| 7 | 9/30/2023 | 73 | |
| 8 | 10/31/2023 | 12/1/2023 | 42 |
| 9 | 12/1/2023 | 12/13/2023 | 11 |
Is there a way to calculate this trending 'snapshotted' data without an actual snapshot? I thought it would be possible to use the Start Date and Completion Dates and TODAY functionality to get to where I need to be but I have not been able to figure this out.
Any help and insights would be greatly appreciated.
As always, thank you community!
Ryan F
Firstly you can create a calculated table as follows.
Date = CALENDAR(MIN('Table'[Actual Start Date]), DATE(2023,12,31))Then you can create two measures as follows.
severalweeksago = IF ( SELECTEDVALUE ( 'Table'[Actual Complete Date] ) > MAX ( 'Date'[Date] ) || SELECTEDVALUE ( 'Table'[Actual Start Date] ) < MAX ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[Actual Complete Date] ) = BLANK (), 1, 0 )DateAge = IF(SELECTEDVALUE('Table'[Actual Complete Date]) > MAX('Date'[Date]) || SELECTEDVALUE('Table'[Actual Complete Date]) = BLANK(), DATEDIFF ( SELECTEDVALUE ( 'Table'[Actual Start Date] ), MAX ( 'Date'[Date] ), DAY ), DATEDIFF( SELECTEDVALUE('Table'[Actual Start Date]), SELECTEDVALUE('Table'[Actual Complete Date]), DAY) )Then put the measure into the filter so that the visual only shows data where the measure is equal to 1.
When the date reaches tomorrow, the today function may not update the date automatically, just click refresh to update it.
Is this the result you expect?
Best Regards,
Community Support Team_Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi,
Please show the expected result very clearly.
Expected results from the small sample above is as follows:
As of Today:
As of today 12/19/23 I would expect to see 3 Open Projects (1 in the 0-30 days past due, 1 in 31-90 Days past due and 1 in 91+ Days). Projects 6,7,10.
Trend of 1 week prior (12/12/23.....TODAY - 7 days)
I would expect to see 4 projects open. Projects 5,6,7,9 in the above example.
Hi,
I still do not understand your expected result. Someone else is already helping you.
Firstly you can create a calculated table as follows.
Date = CALENDAR(MIN('Table'[Actual Start Date]), DATE(2023,12,31))Then you can create two measures as follows.
severalweeksago = IF ( SELECTEDVALUE ( 'Table'[Actual Complete Date] ) > MAX ( 'Date'[Date] ) || SELECTEDVALUE ( 'Table'[Actual Start Date] ) < MAX ( 'Date'[Date] ) && SELECTEDVALUE ( 'Table'[Actual Complete Date] ) = BLANK (), 1, 0 )DateAge = IF(SELECTEDVALUE('Table'[Actual Complete Date]) > MAX('Date'[Date]) || SELECTEDVALUE('Table'[Actual Complete Date]) = BLANK(), DATEDIFF ( SELECTEDVALUE ( 'Table'[Actual Start Date] ), MAX ( 'Date'[Date] ), DAY ), DATEDIFF( SELECTEDVALUE('Table'[Actual Start Date]), SELECTEDVALUE('Table'[Actual Complete Date]), DAY) )Then put the measure into the filter so that the visual only shows data where the measure is equal to 1.
When the date reaches tomorrow, the today function may not update the date automatically, just click refresh to update it.
Is this the result you expect?
Best Regards,
Community Support Team_Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
