Forum Discussion
Calculating %'s from previous months data
Hi, using the data below, I need to show:
- Each Issue % change from the previous month. For example
Aggression: from Jan to Feb = 100%. Feb to March = 75%
Missing People: from Jan to Feb = 300%. Feb to March = -75%
- Each location's total issue increase/decrease % per month. For example
Bristol: Jan to Feb = -75%. Feb to March = 600%
| Location | Issue | Date |
| Bristol | Aggression | 01/01/2025 |
| Bristol | Drug | 01/01/2025 |
| Bristol | Drug | 01/01/2025 |
| Bristol | Drug | 01/01/2025 |
| Manchester | Aggression | 01/01/2025 |
| Manchester | Missing Person | 01/01/2025 |
| Bristol | Drug | 01/02/2025 |
| Manchester | Aggression | 01/02/2025 |
| Manchester | Aggression | 01/02/2025 |
| Manchester | Aggression | 01/02/2025 |
| Manchester | Aggression | 01/02/2025 |
| Manchester | Missing Person | 01/02/2025 |
| Manchester | Missing Person | 01/02/2025 |
| Manchester | Missing Person | 01/02/2025 |
| Manchester | Missing Person | 01/02/2025 |
| Bristol | Aggression | 01/03/2025 |
| Bristol | Aggression | 01/03/2025 |
| Bristol | Aggression | 01/03/2025 |
| Bristol | Aggression | 01/03/2025 |
| Bristol | Aggression | 01/03/2025 |
| Bristol | Drug | 01/03/2025 |
| Bristol | Drug | 01/03/2025 |
| Manchester | Aggression | 01/03/2025 |
| Manchester | Aggression | 01/03/2025 |
| Manchester | Missing Person | 01/03/2025 |
Hi RichOB,
When we want to calculate the number of incidents for a particular Issue and Date combination, we do not need to sum any field. Instead, we should count the number of records (rows) that meet the relevant conditions.
In the original DAX formula provided, where SUM('Table'[Value]) was used, it should be replaced with COUNTROWS('Table'). This function will correctly count the number of rows (incidents) for each Issue and Date, allowing you to then calculate the percentage change over time.
I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.
Thank you.
6 Replies
RichOB , Create a measure using
DAX
% Change =
VAR CurrentIssue = SELECTEDVALUE('Table'[Issue])
VAR CurrentDate = SELECTEDVALUE('Table'[Date])
VAR PreviousDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date] < CurrentDate)
VAR PreviousValue = CALCULATE(SUM('Table'[Value]), 'Table'[Issue] = CurrentIssue, 'Table'[Date] = PreviousDate)
VAR CurrentValue = CALCULATE(SUM('Table'[Value]), 'Table'[Issue] = CurrentIssue, 'Table'[Date] = CurrentDate)
RETURN
IF(ISBLANK(PreviousValue), BLANK(), (CurrentValue - PreviousValue) / PreviousValue)Hi bhanu_gautam
Thanks for your help. What would the SUM('Table'[Value] refer to?Hi RichOB,
When we want to calculate the number of incidents for a particular Issue and Date combination, we do not need to sum any field. Instead, we should count the number of records (rows) that meet the relevant conditions.
In the original DAX formula provided, where SUM('Table'[Value]) was used, it should be replaced with COUNTROWS('Table'). This function will correctly count the number of rows (incidents) for each Issue and Date, allowing you to then calculate the percentage change over time.
I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.
Thank you.
