cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## calculating cumulative and velocity of issues

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.

1 ACCEPTED SOLUTION
Community Champion

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.

• 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.

2 REPLIES 2
Community Champion

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.

• 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.

Helper I

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])
)
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.