DAX - Sales in the last 3 months by salesperson (calculated column)

[levcovitz]

Hello!

I have a table that looks like this:

Month (DD/MM/YYY)	SalespersonID	Sales
01/12/2022	xxxxx	0
01/11/2022	xxxxx	0
01/10/2022	xxxxx	0
01/09/2022	xxxxx	0
01/08/2022	xxxxx	1
01/07/2022	xxxxx	2
01/06/2022	xxxxx	1
01/05/2022	xxxxx	1
01/12/2022	yyyyy	1
01/11/2022	yyyyy	2
01/10/2022	yyyyy	0
01/09/2022	yyyyy	0
01/08/2022	yyyyy	0
01/07/2022	yyyyy	1
01/06/2022	yyyyy	1
01/05/2022	yyyyy	0

I want to create a calculated column that shows how many sales a salesperson made in the previous 3 months (not including the current month). Here's the expected result:

Month (DD/MM/YYY)	SalespersonID	Sales	Sales Previous 3 Months (Expected Result)
01/12/2022	xxxxx	0	0
01/11/2022	xxxxx	0	1
01/10/2022	xxxxx	0	3
01/09/2022	xxxxx	0	4
01/08/2022	xxxxx	1	4
01/07/2022	xxxxx	2	blank
01/06/2022	xxxxx	1	blank
01/05/2022	xxxxx	1	blank
01/12/2022	yyyyy	1	2
01/11/2022	yyyyy	2	0
01/10/2022	yyyyy	0	1
01/09/2022	yyyyy	0	2
01/08/2022	yyyyy	0	2
01/07/2022	yyyyy	1	blank
01/06/2022	yyyyy	1	blank
01/05/2022	yyyyy	0	blank

When there are less than 3 months previous to the date (respecting the salesperson ID context), I don't want to show results.

Any ideas on how to do that in a DAX calculated column?

[ppm1 · Accepted solution]

You should probably do this as a measure, but here is a column expression that seems to work. Replace T2 with your actual table names (and update column names, if needed).

P3Mos =
VAR thisdate = T2[Month]
VAR mindate =
    CALCULATE ( MIN ( T2[Month] ), ALLEXCEPT ( T2, T2[SalespersonID] ) )
VAR prevmonths =
    DATEDIFF ( mindate, thisdate, MONTH )
VAR result =
    CALCULATE (
        SUM ( T2[Sales] ),
        ALLEXCEPT ( T2, T2[SalespersonID] ),
        T2[Month] >= EDATE ( thisdate, -3 )
            && T2[Month] <= EDATE ( thisdate, -1 )
    )
RETURN
    IF ( prevmonths >= 3, result )

Pat

[levcovitz]

Works perfectly. Thank you!