Need help: Measure for % change from the previous quarter

[RichOB]

Hi,

Using the table below, I need to show the % change for the Drugs category in the Incident_Type column from the previous financial quarter to the current one. How can this be done, please? In my real-life scenario, I made a financial quarter column already and I need to isolate the Incident Types by using:

Drugs = (
     Calculate (

     Count(Table[Incident_Type]),

     'Table' [Incident_Type] = "Drugs",
     'Table' [Date]>=DATE(2024,4,1),

     'Table'[DATE]<=DATE(2025,3,31)
))

I need a Matrix table to show me this:

Q1  

Q2 -50%

Q3 100%

Q4 -50%

Date table:

Location	Incident_Type	Incident_Date
Manchester	Drugs	01/04/2024
Manchester	Drugs	01/05/2024
Manchester	Aggression	01/06/2024
Manchester	Aggression	01/07/2024
Manchester	Drugs	01/07/2024
Manchester	Aggression	01/10/2024
Manchester	Drugs	01/10/2024
Manchester	Drugs	01/10/2024
Manchester	Drugs	01/02/2024

Thanks for your help

[danextian · Accepted solution]

Hi @RichOB 

A proper dates table would make the calculation simpler.  Ensure that the table has marked as a dates table.

That aside our results don't match. The table below follows this formula to get the % change

( Current Quarter - Previous Quarter )/ Previous Quarter

Please see the attached sample pbix.

[DataNinja777 · Accepted solution]

Hi @RichOB ,

In order to utilize the time intellgence function, first you will need to create a calendar table like below:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(ROUNDUP(MONTH([Date]) / 3, 0), "0"),
    "Financial Quarter", "Q" & FORMAT(ROUNDUP(MOD(MONTH([Date]) - 3, 12) / 3 + 1, 0), "0")
)

To calculate the percentage change for the "Drugs" category in your Power BI Matrix table by financial quarter, you need to create a few measures in DAX that count incidents and calculate the percentage change from the previous quarter. Since you already have a financial quarter column created in your Date table, we'll proceed directly to creating the necessary DAX measures.

First, create a measure to count the number of incidents where the Incident_Type is "Drugs." This can be achieved using the CALCULATE function to filter only those rows that match the "Drugs" incident type. The measure will look like this:

Drugs Incidents =
CALCULATE(
    COUNT('Table'[Incident_Type]),
    'Table'[Incident_Type] = "Drugs"
)

Next, you need to create a measure that calculates the percentage change from the previous quarter. For this, use the PREVIOUSQUARTER function to retrieve the count of "Drugs" incidents from the prior quarter and compare it to the current quarter’s count. The formula should handle cases where the previous quarter's count is zero to avoid division errors. Here's the measure for percentage change:

% Change Drugs = 
VAR CurrentCount = [Drugs Incidents]
VAR PreviousCount = CALCULATE(
    [Drugs Incidents],
    PREVIOUSQUARTER('Calendar'[Date])
)
RETURN
IF(
    NOT ISBLANK(PreviousCount),
    DIVIDE(CurrentCount - PreviousCount, PreviousCount, 0),
    BLANK()
)

After creating these measures, add a Matrix visual in Power BI. In the Rows field, use the Financial Quarter column from your Date table to display the data by quarters. In the Values field, add the Drugs Incidents measure to show the count of incidents for each quarter, and the % Change Drugs measure to display the quarter-over-quarter percentage change. The Matrix will display results like this:

I have attachd an example pbix file for your reference.

Best regards,

[danextian · Accepted solution]

Hi @RichOB 

A proper dates table would make the calculation simpler.  Ensure that the table has marked as a dates table.

That aside our results don't match. The table below follows this formula to get the % change

( Current Quarter - Previous Quarter )/ Previous Quarter

Please see the attached sample pbix.