Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank You so much. The solution worked.
User | Count |
---|---|
50 | |
44 | |
18 | |
14 | |
14 |
User | Count |
---|---|
107 | |
58 | |
29 | |
21 | |
16 |