Home Forums Power Pivot Calculate Days Outstanding by Period

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 1 month ago.

Viewing 2 posts - 1 through 2 (of 2 total)
• Author
Posts
• #9594

MCSmith1950
Participant
• Started: 1
• Replies: 0
• Total: 1

I’m trying to create a series of box plots by end of sprint that show the days outstanding of the backlog at that point in time where Days Outstanding is duration from Date Created to End of Month Date.

I have two tables of data:
1) a table of work records (Feature Fact Table) with Work Record ID, Date Created, Date Pulled
2) a Calendar (SprintCalendar) of dates with DateKey, DateIndex, SprintIndex

The desired pivot table would contain Rows for each SprintID and Column Measures – End of Sprint Date, Work Rec Count, and Days Outstanding (Min, 25% Percentile, Median, 75% percentile, Max, Avg)

I’ve got the Sprint ID rows, End of Sprint Date, and Record Count working with any issues. The following Record Count measure is working correctly.

=CALCULATE(COUNTROWS(‘Feature Fact Table’),
FILTER(‘Feature Fact Table’,’Feature Fact Table'[Created Date]<=LASTDATE(‘SprintCalendar'[DateKey])
&& (‘Feature Fact Table'[Pulled Date] = BLANK()
|| ‘Feature Fact Table'[Pulled Date]> LASTDATE(‘SprintCalendar'[DateKey]))
)
)

So I was thinking that I would need to replace the COUNTROWS in the above formula with a measure that calculated the appropriate Days Outstanding as
AERAGE(LASTDATE(‘SprintCalendar'[DateKey] – ‘Feature Fact Table'[Created Date]).

How do I go about making this work?

Thanks,
Mike

#9595

tomallan
Keymaster
• Started: 9
• Replies: 2244
• Total: 2253

Mike,

Sounds like you are heading in the right direction, but you might be making things hard for yourself.

First, you sample formula could probably be shortened by just doing a COUNTROWS against the FILTER expression:

``````= COUNTROWS (
FILTER (
'Feature Fact Table',
'Feature Fact Table'[Created Date] <= LASTDATE ( 'SprintCalendar'[DateKey] )
&& (
'Feature Fact Table'[Pulled Date] = BLANK ()
|| 'Feature Fact Table'[Pulled Date] > LASTDATE ( 'SprintCalendar'[DateKey] )
)
)
)``````

Could you put up some sample data?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.