Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to 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:
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])))
This formula sums up the 'delta' values for all weeks up to the current week.
Create a Velocity column:
Now, create another new column named "Velocity."
Use the following DAX formula:
Velocity = 'YourTable'[Cumulative Delta] / 'YourTable'[tickets opened]
This formula calculates the ratio of 'Cumulative Delta' to 'tickets opened' for each row.
Visualize your data:
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.
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:
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])))
This formula sums up the 'delta' values for all weeks up to the current week.
Create a Velocity column:
Now, create another new column named "Velocity."
Use the following DAX formula:
Velocity = 'YourTable'[Cumulative Delta] / 'YourTable'[tickets opened]
This formula calculates the ratio of 'Cumulative Delta' to 'tickets opened' for each row.
Visualize your data:
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.
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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |