How to get the total sales for the previous year > than a given amount

[sktwr61622]

I need help to figure out how to display the total sales for the previous year based on the slicer which is current year and the sales for the previous year must be greater than a certain amount

I have 2 tables: a date table and a Sales table

Sales Table

SalesRep Date Amount
101 2022-01-15 250
102 2022-01-25 500
101 2022-02-10 1,000
102 2022-02-20 100
103 2022-02-22 5,000
101 2021-01-10 2,000
102 2021-01-15 200
101 2021-02-11 4,000
102 2021-02-10 200
103 2021-02-12 6,000

I have a date slicer set for 2022-01-01 to 2022-02-28 and this is the only slicer on the report

I want to find the total sales for last year for the same time period as the slicer and the total amount for each sales rep > 3,000 and then sum all the records that meet the criteria

The interim result if I will to sum it by year will be

Var SLS = SUM('Sales'[Amount])

CALCULATE([SLS], SAMEPERIODLASTYEAR('DIM_Date'[DTE_Date]))

Sales Rep Year Amount
101 2022 1,250
102 2022 600
103 2022 5,000

101 2021 6,000
102 2021 400
103 2021 6,000

Since only sales rep 101 and 102 > 3,000 for the previous year, I want the result and exclude 102 since it is < 3,000

My final answer should be for previous year = 12,000

Thank You in advance for your assistance

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

All measures are in the attached pbix file.

Current year sales: = 
SUM( Data[Amount] )

Previous year same period sales: = 
CALCULATE ( [Current year sales:], DATEADD ( 'Calendar'[Date], -1, YEAR ) )

P.Y same period sales by the conditoin: = 
SUMX (
    FILTER (
        VALUES ( SalesRep[SalesRep] ),
        [Previous year same period sales:] > 3000
    ),
    [Previous year same period sales:]
)

[sktwr61622]

Thank You so much. The solution worked.