Try to calculate for X month this year and compare to x month last year in flexible way

[E_K_]

Hi

I'm trying to create 2 columns that will show for example March 2022 vs March 2023 stats for a team when you filter on a specific month.

What I have at the moment is this (I have a 'Dates' table and the team specific stats is calculated from data in 'Incidents' throug a calculated measure). It doesn't work 🙂

MTTR specific month =

VAR StartDate = STARTOFMONTH('Dates'[Date])

VAR EndDate = ENDOFMONTH('Dates'[Date])

RETURN

CALCULATE([MTTR], DATESBETWEEN('Dates'[Date], StartDate, EndDate))

However when I filter for the use case, the matrix does not remove all the respective columns to just show January - is there something I need to do with the matrix or another measure for the months in the matrix to be flexible? At the moment it is just filtering on teams have have a stat for bth January 2022 & January 2023. The column on the far left is just a flat overall [MTTR] to compare with.

  

[v-eqin-msft · Accepted solution]

Hi @E_K_ ,

Due to I don't know your data model, I will give you some suggestions. According to your statement, I think you only want to compare the data current month with the data on same period in pervious year.

Her I create a sample to have a test.

Measure:

MTTR specific month =
VAR _currentyear =
    YEAR ( TODAY () )
VAR _selectmonth =
    SELECTEDVALUE ( DimMonth[Month] )
RETURN
    IF (
        ISFILTERED ( DimMonth[MonthName] ),
        CALCULATE (
            [MTTR],
            FILTER (
                'Calendar',
                'Calendar'[Year]
                    IN { _currentyear, _currentyear - 1 }
                    && 'Calendar'[Month] = _selectmonth
            )
        ),
        [MTTR]
    )

Result is as below.

Best Regards,

Rico Zhou

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