Calculating %'s from previous months data

[RichOB]

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

[bhanu_gautam]

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

[RichOB]

Hi @bhanu_gautam 

Thanks for your help. What would the SUM('Table'[Value] refer to?

[v-sgandrathi · Accepted solution]

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.

[v-sgandrathi]

Hi @RichOB,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @RichOB,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi  @RichOB 

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!