calculate % difference from previous quarter

Hello Marshmallow,

Can you please try this approach:

1. create a measure that counts the number of "LOW" performers

LowCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "LOW"
)

2. create a measure for counting "MOD" (Moderate) performers

ModerateCount = 
CALCULATE(
    COUNTROWS('YourTable'),
    'YourTable'[Rating] = "MOD"
)

3. for each rating, create a measure to get the count of the previous quarter

LowCount_PrevQuarter = 
CALCULATE(
    [LowCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

ModerateCount_PrevQuarter = 
CALCULATE(
    [ModerateCount],
    PREVIOUSQUARTER('YourTable'[Period_1])
)

4. calculate the percentage change compared to the previous quarter

Low_Percent_Change = 
IF(
    NOT(ISBLANK([LowCount_PrevQuarter])),
    DIVIDE([LowCount] - [LowCount_PrevQuarter], [LowCount_PrevQuarter], 0),
    BLANK()
)

Moderate_Percent_Change = 
IF(
    NOT(ISBLANK([ModerateCount_PrevQuarter])),
    DIVIDE([ModerateCount] - [ModerateCount_PrevQuarter], [ModerateCount_PrevQuarter], 0),
    BLANK()
)

5. now you can, create a Narrative Measure

Narrative = 
VAR CurrentPeriod = SELECTEDVALUE('YourTable'[Period_1])
VAR LowCurrent = [LowCount]
VAR LowPrevious = [LowCount_PrevQuarter]
VAR LowPercentChange = [Low_Percent_Change] * 100

VAR ModerateCurrent = [ModerateCount]
VAR ModeratePrevious = [ModerateCount_PrevQuarter]
VAR ModeratePercentChange = [Moderate_Percent_Change] * 100

RETURN 
SWITCH(
    TRUE(),
    CurrentPeriod = "Jun 23", "Insufficient data",
    CurrentPeriod = "All period" || CurrentPeriod = "Jun 24",
        "In Jun 24, there were " & LowCurrent & " low performers (" & 
        IF(ISBLANK(LowPercentChange), "no change", 
            FORMAT(LowPercentChange, "0") & "% " & 
            IF(LowPercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & 
        "); " & ModerateCurrent & " moderate performers (" & 
        IF(ISBLANK(ModeratePercentChange), "no change", 
            FORMAT(ModeratePercentChange, "0") & "% " & 
            IF(ModeratePercentChange < 0, "reduction", "increase") & 
            " compared to Mar 24"
        ) & ").",
    "In " & CurrentPeriod & ", there were " & LowCurrent & " low performers (" & 
    IF(ISBLANK(LowPercentChange), "no change", 
        FORMAT(LowPercentChange, "0") & "% " & 
        IF(LowPercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & 
    "); " & ModerateCurrent & " moderate performers (" & 
    IF(ISBLANK(ModeratePercentChange), "no change", 
        FORMAT(ModeratePercentChange, "0") & "% " & 
        IF(ModeratePercentChange < 0, "reduction", "increase") & 
        " compared to the previous quarter"
    ) & ")."
)

Hope this helps.

Hi Marshmallow ,

You can achieve your required output by writing a variance commentary dax measure like below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    VAR PreviousQuarterDate = CALCULATE(MAX('Calendar'[Date]), DATEADD('Calendar'[Date], -1, QUARTER))
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount) && CurrentLowPerformerCount < PreviousLowPerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousLowPerformerCount),
                "an increase of ",
                ""
            )
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount) && CurrentModeratePerformerCount < PreviousModeratePerformerCount,
            "a reduction of ",
            IF(
                NOT ISBLANK(PreviousModeratePerformerCount),
                "an increase of ",
                ""
            )
        )

    RETURN
        "In " & CurrentQuarter & ", there were " & 
        CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
        " compared to the previous quarter); " & 
        CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
        " compared to the previous quarter)."

The output would look as follows:

I have attached an example pbix for your reference.

Best regards,

Hi Marshmallow ,

You can produce your desired output by tweaking the DAX formula as shown below:

Commentary = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers and " & 
            TotalModeratePerformerCount & " moderate performers.",
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & LowPerformerTrend & LowPerformerChange & 
            " compared to the previous quarter); and " & 
            CurrentModeratePerformerCount & " moderate performers (" & ModeratePerformerTrend & ModeratePerformerChange & 
            " compared to the previous quarter)."
        )

The resulting output when no filter is applied is shown below:

I have attached an example pbix file for your reference.

Best regards,

Hi Marshmallow ,

You can produce your required output by writing the dax formula like below.

Commentary (updated) = 
    VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY Quarter])
    
    VAR CurrentLowPerformerCount = [Low performer count]
    VAR CurrentModeratePerformerCount = [Moderate performer count]
    VAR CurrentHighPerformerCount = [High performer count]
    
    VAR PreviousLowPerformerCount = 
        CALCULATE(
            [Low performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
    
    VAR PreviousModeratePerformerCount = 
        CALCULATE(
            [Moderate performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )
        
    VAR PreviousHighPerformerCount = 
        CALCULATE(
            [High performer count],
            DATEADD('Calendar'[Date], -1, QUARTER)
        )

    VAR LowPerformerChange = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentLowPerformerCount - PreviousLowPerformerCount, PreviousLowPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR ModeratePerformerChange = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            FORMAT(ABS(DIVIDE(CurrentModeratePerformerCount - PreviousModeratePerformerCount, PreviousModeratePerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR HighPerformerChange = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            FORMAT(ABS(DIVIDE(CurrentHighPerformerCount - PreviousHighPerformerCount, PreviousHighPerformerCount, 0)), "0%"),
            "insufficient data"
        )

    VAR LowPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousLowPerformerCount),
            IF(CurrentLowPerformerCount < PreviousLowPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR ModeratePerformerTrend = 
        IF(
            NOT ISBLANK(PreviousModeratePerformerCount),
            IF(CurrentModeratePerformerCount < PreviousModeratePerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR HighPerformerTrend = 
        IF(
            NOT ISBLANK(PreviousHighPerformerCount),
            IF(CurrentHighPerformerCount < PreviousHighPerformerCount, "a reduction of ", "an increase of "),
            ""
        )

    VAR MinQuarter = CALCULATE(MIN('Calendar'[YYYY Quarter]), ALL('Calendar'))
    VAR MaxQuarter = CALCULATE(MAX('Calendar'[YYYY Quarter]), ALL('Calendar'))
    
    VAR TotalLowPerformerCount = CALCULATE([Low performer count], ALL('Calendar'))
    VAR TotalModeratePerformerCount = CALCULATE([Moderate performer count], ALL('Calendar'))
    VAR TotalHighPerformerCount = CALCULATE([High performer count], ALL('Calendar'))

    RETURN
        IF(
            ISBLANK(CurrentQuarter),
            "In " & MinQuarter & " to " & MaxQuarter & 
            ", there were " & TotalLowPerformerCount & " low performers, " & 
            TotalModeratePerformerCount & " moderate performers, and " & 
            TotalHighPerformerCount & " high performers.",
            
            "In " & CurrentQuarter & ", there were " & 
            CurrentLowPerformerCount & " low performers (" & 
            IF(
                LowPerformerChange = "0%",
                "no change to previous quarter",
                LowPerformerTrend & LowPerformerChange & " compared to the previous quarter"
            ) & "); " &
            
            CurrentModeratePerformerCount & " moderate performers (" & 
            IF(
                ModeratePerformerChange = "0%",
                "no change to previous quarter",
                ModeratePerformerTrend & ModeratePerformerChange & " compared to the previous quarter"
            ) & "); and " &
            
            CurrentHighPerformerCount & " high performers (" & 
            IF(
                HighPerformerChange = "0%",
                "no change to previous quarter",
                HighPerformerTrend & HighPerformerChange & " compared to the previous quarter"
            ) & ")."
        )

The resulting output is as shown below:

I have attached an example pbix file for your reference.

Best regards,