Using the stacked bar chart to win loss streaks over time

[c8linoneil]

Hi all,

I am trying to create a visual like this in Power BI Desktop. This visual will, hopefully, measure the win/loss streak of a specific team over time.

For example, if we were look at the year, january would be at the bottom of the y-axis and decemeber at the top with the stacks representing how many days the team had a win streak, loss streak, etc.

I do not want something where it counts the total days won and lost for each team (two sections per bar). I'm hoping to find a solution that will represent how long each streak has been throughout the year.

To do that I know I need a table similar to this one. 

The data I am using contains an ID (think team name) and the dates that they have won (1) and lost (0). I have a measure that calculates the total days they have won, total days they have lost, and total days overall.

How should I go about calculating the # of streak days, associating that number with a specific streak (streak 1, 2, 3, etc.), and assigning win or loss? 

Any and all help would be appreciated 🙂

[c8linoneil]

Hi @v-jingzhan-msft 

The first table is a sample of the current data I have. The dates for each team go on for longer in the actual data. The last column represents win(1) and loss(0).

ID	Team Name	Date	Result
1	Lions	1/1/2024	1
1	Lions	1/2/2024	0
1	Lions	1/3/2024	0
1	Lions	1/4/2024	1
1	Lions	1/5/2024	1
2	Tigers	1/1/2024	0
2	Tigers	1/2/2024	1
2	Tigers	1/3/2024	0
2	Tigers	1/4/2024	1
2	Tigers	1/5/2024	1
3	Bears	1/1/2024	1
3	Bears	1/2/2024	1
3	Bears	1/3/2024	0
3	Bears	1/4/2024	0
3	Bears	1/5/2024	0

The second table reflects how I would like to transform that data. I view Streak ID as an index. This column is necessary to create the stacked bar visual.

ID	Streak ID	Streak # of Days	Type of Streak
1	Streak 1	1	1
1	Streak 2	2	0
1	Streak 3	2	1
2	Streak 4	1	0
2	Streak 5	1	1
2	Streak 6	1	0
2	Streak 7	2	1
3	Streak 8	2	1
3	Streak 9	3	0

Hopefully this can give a little more insight!

[v-jingzhan-msft]

Hi @c8linoneil 

Can you share some dummy data including the Team ID and the dates that they have won (1) and lost (0)? And Can you describe the logic to calculate the # of streak days? To achieve your goal, it seems we need to calculate the # of streak days in each month for every team. Currently I don't understand how the # of streak days is calculated and what "Streak ID" means? Can you provide some more information about this?

Best Regards,
Jing