Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
aherabit
Helper I
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.

weektickets openedtickets closeddeltacumulative deltavelocity (cumulative / opened)
1109-1-1-0.10
2108-2-3-0.15
3107-3-6-0.20
410133-3-0.08
510100-3-0.06
6107-3-6-0.10
710133-3-0.04
81015520.03
9109-110.01
10109-100.00
111012220.02
12107-3-1-0.01
13106-4-5-0.04
1410122-3-0.02
151015520.01
16106-4-2-0.01
 160158-2-29 

 

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

 

Appreciate any pointers.

1 ACCEPTED SOLUTION
123abc
Community Champion
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.

  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

1109-1-1-0.1
2108-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.

View solution in original post

2 REPLIES 2
123abc
Community Champion
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.

  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

1109-1-1-0.1
2108-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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.