calculating cumulative and velocity of issues

[aherabit]

Hi Folks,

I have the following example and need help:
Both, 'tickets opened' and 'tickets closed' are 'calculate' measures.

week	tickets opened	tickets closed	delta	cumulative delta	velocity (cumulative / opened)
1	10	9	-1	-1	-0.10
2	10	8	-2	-3	-0.15
3	10	7	-3	-6	-0.20
4	10	13	3	-3	-0.08
5	10	10	0	-3	-0.06
6	10	7	-3	-6	-0.10
7	10	13	3	-3	-0.04
8	10	15	5	2	0.03
9	10	9	-1	1	0.01
10	10	9	-1	0	0.00
11	10	12	2	2	0.02
12	10	7	-3	-1	-0.01
13	10	6	-4	-5	-0.04
14	10	12	2	-3	-0.02
15	10	15	5	2	0.01
16	10	6	-4	-2	-0.01
	160	158	-2	-29

How can I approach it?  Especially, in calculating the 'cumulative delta'

Appreciate any pointers.

[123abc · Accepted solution]

In Power BI, you can achieve this by creating calculated columns and measures. Here's a step-by-step guide on how to calculate the 'cumulative delta' and 'velocity' in Power BI:

1. Create a Cumulative Delta column:

   • Go to your data model in Power BI.

   • In the table where your data is, create a new column named "Cumulative Delta."

   • Use the following DAX formula:

Cumulative Delta = CALCULATE(SUM('YourTable'[delta]), FILTER(ALL('YourTable'), 'YourTable'[week] <= EARLIER('YourTable'[week])))

1. This formula sums up the 'delta' values for all weeks up to the current week.

2. Create a Velocity column:

   • Now, create another new column named "Velocity."

   • Use the following DAX formula:

Velocity = 'YourTable'[Cumulative Delta] / 'YourTable'[tickets opened]

1. This formula calculates the ratio of 'Cumulative Delta' to 'tickets opened' for each row.

2. Visualize your data:

   • Use a table or chart to visualize the 'Cumulative Delta' and 'Velocity' over time.

Here's an example of what your Power BI table might look like:

week tickets opened tickets closed delta Cumulative Delta Velocity

1	10	9	-1	-1	-0.1
2	10	8	-2	-3	-0.15
...	...	...	...	...	...

Remember to replace 'YourTable' with the actual name of your table in Power BI.

After creating these columns, you can use them in your reports and dashboards to visualize the cumulative delta and velocity trends over time.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[aherabit]

Hitting this on the 'Date' table.  Any idea how to overcome it?
My 'Date' table has WeekNumber column with autogenerated values with Weeknum()

A single value for column 'WeekNumber' in table 'DateTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.


Here is my query:

Cummulative_Velocity_delta =

CALCULATE(

    SUM('MyTable'[Velocity_delta]),

    FILTER(

        ALL('MyTable'),

        DateTable[WeekNumber] <= EARLIER(DateTable[WeekNumber])

    )

)