Running Total grouped by cumulative

[pbi_wannabe_exp]

Hi folks!

I have a complicated task that I want to solve.

Here is the sample data: I would like to know the sprint daily(!) count(key) where issue_type = "task" on a year, sprint, day of sprint view with running totals (1 sprint is 2 weeks, the info is added in column from merged tables)

This is how the matrix should look with the running total:

I would like to compare the cumulative change between current sprint (in this case spint 04) with the cumulative change previous sprint same day of sprint.

1. e.g.: Sprint 04 day 10 = (8(running total sprint 04) - 8 (running total sprint 03 --> becasue only the data from day 6 is available)) / 8 (running total sprint 03) --> in this specfic case blank

2. e.g.: Sprint 04 day 5 = (3 - 7) / 7 = -0.57

I would be able to change the sprint number filter and the day of sprint would be always the last available day of sprint within a sprint.

I dont know why I cant attach the pbi as well where I tried to create the running total with no luck.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to solve this is to have a proper dim-calendar table.

I believe you have one that shows together with Sprint year / wk / day columns.

I am not 100% sure whether I created a correct dim-calendar table that suits your business, but I hope the below can provide ideas how to create measures and apply those to your data model.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

 

[pbi_wannabe_exp]

Jihwan, many thanks for your help! Your solution is exactly what I was looking for.