Attrition Rate for Same Period Last Year

[Edirin]

Is there any way to calculate / compare the attrition rate for the sam month as last year? For example April 2024 vs April 2023. I'm try to show them in a card visual and have it update each month. What i'm getting just seems to show the whole of 2023. Is this possible?

[v-jiewu-msft · Accepted solution]

Hi @Edirin ,

Based on my testing, please try the following methods as workaround:

1.Create the simple table.

2.Create the new measure to calculate current year attrition.

current year attrition = 
VAR tota = CALCULATE (
        SUM ('Table'[Number]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY())
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR _attri = CALCULATE (
        SUM ('Table'[Attrition]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY())
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR result = DIVIDE(_attri, tota)
RETURN
result

3.Create the new measure to calculate last year attrition.

Lat year attrition = 
VAR tota = CALCULATE (
        SUM ('Table'[Number]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY()) - 1
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR _attri = CALCULATE (
        SUM ('Table'[Attrition]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY()) - 1
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR result = DIVIDE(_attri, tota)
RETURN
result

4.Change the two measures format to the percentage.

5.Drag the measure into the Multi-row card visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This pattern should work

Attrition SPLY = calculate([Total attrition],sameperiodlastyear(calendar[date]))

Ensure that in the slicer you drag Year and Month name from the Calendar table and select a month there.

[v-jiewu-msft · Accepted solution]

Hi @Edirin ,

Based on my testing, please try the following methods as workaround:

1.Create the simple table.

2.Create the new measure to calculate current year attrition.

current year attrition = 
VAR tota = CALCULATE (
        SUM ('Table'[Number]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY())
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR _attri = CALCULATE (
        SUM ('Table'[Attrition]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY())
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR result = DIVIDE(_attri, tota)
RETURN
result

3.Create the new measure to calculate last year attrition.

Lat year attrition = 
VAR tota = CALCULATE (
        SUM ('Table'[Number]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY()) - 1
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR _attri = CALCULATE (
        SUM ('Table'[Attrition]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] = YEAR(TODAY()) - 1
                && 'Table'[Month] = MONTH(TODAY())
        )
    )
VAR result = DIVIDE(_attri, tota)
RETURN
result

4.Change the two measures format to the percentage.

5.Drag the measure into the Multi-row card visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.