0

I'm creating a dashboard for a sprint overview and want to visualize the progress of the team in a burndown chart. The chart should give information about the amount of user stories that are each new, active and closed. So that in the beginning all user stories are open, then they become active and in the end, there are no open and active stories left.

Now my problem is in modeling the data using DAX. The data is stored in a big table with a row for each user story that contains all the information on that date. Now, if the information gets modified like in the event of changing the status from new to active or correcting a spelling mistake, the program just adds a new row with a new date.

like in here

The table I want should have the columns date, new, active, and closed. For the date column i have written the following code:

 CALENDAR(
    FIRSTNONBLANK(
        CurrentIterations[StartDate];
        CurrentIterations[StartDate] );
    FIRSTNONBLANK(
        CurrentIterations[FinishDate];
        CurrentIterations[FinishDate])
    )

But now, oriented on that dates i want the other columns to calculate themselves. In every row I want the amount of user stories in the original table that are active and the latest version on that date.

Examples:

Original table

original table

Wanted table

wanted table

Wanted burndown chart

wanted burndown chart

Any help greatly appreciated!

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
J. Wachlin
  • 15
  • 1
  • 6
  • Can you post a sample of what you want your output table to look like? At least a couple rows? – Alexis Olson Mar 19 '18 at 15:37
  • Done :) Its a new more simple example:) – J. Wachlin Mar 20 '18 at 10:52
  • Can you explain why you have 2 for `Active` in the `03.01.2018` row of the `Wanted` table? – Alexis Olson Mar 20 '18 at 16:37
  • I don't quite understand the logic from input to output. Can you clarify? – Alexis Olson Mar 20 '18 at 20:06
  • The original table is kind of a Changelog of the user stories. Every time a userstory is created, edited or deleted, a row is added to the table. Because i want an overview of the teams progress, I need to know what the latest status of all the stories on a certain date was. On the 3.1.18 there were 2 stories active - story with id 3 and story with id 2. Story 2 was set active one day earlier but the status has not changed until the 3rd January. – J. Wachlin Mar 21 '18 at 08:56

2 Answers2

0

Well, it isn't as beatiful, but it does the job. I've created an extra table, to get the last Rev per Id and Day. At least, that's what I thought you meant.

I'm open for better solutions, I'm convinced it can be done better/easier.

'Test' is your original table, make a link on Date to your already created table (Wanted) with the Date column.

Calculated table:

MaxRev = 
SUMMARIZE(Test; Tabel[Date]; Test[Id]; "Max"; MAX(Test[Rev]))

Column New (add to table with single date column):

New = 
CALCULATE (    
DISTINCTCOUNT ( Test[Id] );
CALCULATETABLE (
    FILTER (
        CROSSJOIN ( 'MaxRev'; Test );
        'MaxRev'[Id] = Test[Id]
            && 'MaxRev'[Date] = Test[Date]
            && 'MaxRev'[Max] = Test[Rev]
    )
);
Test[State] = "New"
) + 0

Repeat this column, also for Active and Solved.

Result

PBIX file

TJ_
  • 629
  • 6
  • 12
0

Here's another approach.

Calculated table:

MaxRev = CROSSJOIN(VALUES(Test[Id]), VALUES(Test[Date]))

Add the following calculated columns to this table:

MaxRev = CALCULATE(MAX(Test[Rev]),
             FILTER(Test, Test[Id] = MaxRev[Id] && Test[Date] <= MaxRev[Date]))

Status = LOOKUPVALUE(Test[State], Test[Id], MaxRev[Id], Test[Rev], MaxRev[MaxRev])

Then use this to create a new calculated table:

Wanted = SUMMARIZE(MaxRev, MaxRev[Date],
            "New", CALCULATE(COUNT(MaxRev[Id]), MaxRev[Status] = "New") + 0,
            "Active", CALCULATE(COUNT(MaxRev[Id]), MaxRev[Status] = "Active") + 0,
            "Solved", CALCULATE(COUNT(MaxRev[Id]), MaxRev[Status] = "Solved") + 0)
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64