Monthly snap shop of user stories in the backlog (active user stories) using DAX

[Anonymous]

Hi there, very new to PowerBI.

I'd like to track/take a snapshot of data on a monthly basis so I can show a trend for the number of user stories in a backlog.  Basically, I want to know if the backlog is increasing or decreasing.

	User Stories in Backlog
Aug 1	43
Sept 1	42
Oct 11	42
Nov 1	39
Dec 1	39

Would you create using DAX Measures?

User Stories that have a State = Open, In Review, In Progress, Ready for Technical Review or In Grooming will show up in the Backlog

The dataset is coming from Azure DevOps.

Any suggestions?

[v-jianboli-msft]

Hi @Anonymous ,

Here is my sample:

I'm still not quite sure exactly what kind of problem you're talking about?
What exactly does overwriting the previous value mean? If you can, please provide me with a sample or screenshot of the sensitive data removed to help me better understand your question.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @Anonymous ,

Sorry, I can't reproduce your question just by your description, how many columns in your original table are related to the date? Do you have a date dimension table and if so, which field is the relationship between it and the fact table based on?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Jianbo, sorry for the late response.

To explain things a little bit further, I am trying to create a time based snap shot of data within a table but the data changes on a daily basis.  For example:

- There are 25 User Stories in the backlog at the end of week 1

- There are 28 User Stories in the backlog at the end of week 2

- There are 32 User Stories in the backlog at the end of week 3

- There are 23 User Stories in the backlog at the end of week 4

Currently there are only 15 User Stories in the backlog

If we run the current dax based on what is in the backlog today, we only get a value of 15.  We need to stamp the values of what was in the backlog at the end of each week or other time period (month)

How do we stamp each value and make sure we are not overwriting the previous value and just adding to it with the following weeks stamp?

Sorry if that doesn't make sense. Let me know what you think.

Cheers

[Anonymous]

Hi Jianbo, thanks for this!

I'm not sure its giving the exact output I'm looking for and I may have not provided all the necessary information.

I created the DAX Measure and it does create a visual, but only one bar (bar chart) showing the current count of User Stories in the Backlog.  Now if I add a Date X axis, it shows values per month but these values are incorrect.

So without a Date x-axis it shows the correct current value but trying to show by month, it does not work using/trying different Date parameters.

Perhaps I need to use a particular Date x axis such as State Changed Date?  Even when I used this, it does not produce the correct counts for each month.

Maybe we need to adjust the measure to look at State Changed Date and if that value was 

"Open", "In Review", "In Progress", "Ready for Technical Review", "In Grooming" during a particular month/period?

Any ideas?

Thanks!

[v-jianboli-msft]

Hi @Anonymous ,

Based on your description, I have created a simple sample:

Please try:

User Stories in Backlog = CALCULATE(COUNT('Table'[User Story ID]),FILTER('Table',[State] in {"Open", "In Review", "In Progress", "Ready for Technical Review", "In Grooming"}))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.