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.