Burndown chart for software tasks

[Dni]

Hello,

I'm trying to create a table containing data that will be used to create a burndown chart. As an inputs, I have two tables:

1. Dates: the table date corresponding to the list of dates for a given release/sprint

2. History: the table containing the transitions for the set of tasks to be performed

       The fields are: 

            id: id of the task

            when: transition date between to states

            status: status of a task (New, Assigned, Analyzed, Solving, etc)

            It looks like this:

id when state

1	2/1/2024	New
1	2/2/2024	Assigned
2	3/1/2024	New
3	2/2/2024	Solving
2	4/1/2024	Solving

I'm trying to build a Burndown table from these datas using DAX. I don't want a measure here.

Date	New	Assigned	Analyzed
1/1/2024	1
2/1/2024	2	1
3/1/2024	1	1	1
4/1/2024	0
5/1/2024	...

New = number of tickets with last 'when' is "New" before the date.

I've been circling around a solution without converging for quite a some time, hence a little help would be highly appreciated.

Thanks in advance for any advice you may give!

[Dni]

Hello,

thanks for the quick reply

Here is an example of something that is in a measure; same spirit:

    VAR _Summarized = SUMMARIZE(

        _History ,

        'History'[id] ,

        "@Last Date", MAX('History'[when] ) ,

        "@Last Status" , CALCULATE(

                            MAX('History'[status]),

                            FILTER(

                                _History,

                                'History'[when]= MAX ('History'[when])

                            )

                        )

    )  

Basically for each date in the calculated table I want to:

- Filter History to get only the dates before the reference date

(e.g. the first date in the Burndown table is 1/1/24. This is the reference date)

- Filter History to get the latest [when] for each id

(e.g if I have a when for id1 at 31/12/23 and 30/12/23, I'll keep only the 31/12/23 one)

- and Finally filter history to get the id in status New

and then, count the IDs that match these criteria, and fill the New with this value in Burndown

[govindarajan_d]

Hi @Dni ,

New = number of tickets with last 'when' is "New" before the date. I am not able to understand this. Can you help us by posting a single calculation and how it is done?

[Dni]

Hello,

thanks for the quick reply

Here is an example of something that is in a measure; same spirit:

    VAR _Summarized = SUMMARIZE(

        _History ,

        'History'[id] ,

        "@Last Date", MAX('History'[when] ) ,

        "@Last Status" , CALCULATE(

                            MAX('History'[status]),

                            FILTER(

                                _History,

                                'History'[when]= MAX ('History'[when])

                            )

                        )

    )  

Basically for each date in the calculated table I want to:

- Filter History to get only the dates before the reference date

(e.g. the first date in the Burndown table is 1/1/24. This is the reference date)

- Filter History to get the latest [when] for each id

(e.g if I have a when for id1 at 31/12/23 and 30/12/23, I'll keep only the 31/12/23 one)

- and Finally filter history to get the id in status New

and then, count the IDs that match these criteria, and fill the New with this value in Burndown