User entered date with comparison column for previous year

[wilberk]

I am trying to have a custom date range that the user can enter dates and I want a column that shows the values for that current date range and a second column that shows the values for that month/day but for the previous year. So for example I have a sold date set to 6/1/2023- 6/15/2023 that the user entered and a column showing this year sold amount of 1000. I want the second column to show the sold amount of last year 6/1/2022-6/15/2022 lets say 800. I am trying to use a matrix visual to do this and have slicers for users to choose their start and end date. I have experience with tableau so there I could enter a date parameter and set calculations to compare to the start and end date parameters and subtract a year from those parameters that the users were entering themselves. Looking for something similar in power bi. 

[lbendlin]

a custom date range that the user can enter dates

I am trying to use a matrix visual to do this and have slicers for users to choose their start and end date.

These statements are ambiguous. Are you planning to allow your users to manually enter a date, or are you restricting them to have to select an existing date?

[wilberk]

I want the user to manually enter the date they desire for this year and get the comparison between this year and last years data in the matrix 

 

[lbendlin]

There is no good visual for manually entering data in Power BI.  Will you be using the filter pane with advanced mode filters?

[wilberk]

If there is a way for it to work with the filter pane with advanced mode filters I can use that. 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

[wilberk]

Sample Data:

clothing group	clothing category	date of purchase	Amount
shirts	long sleeve shirt	4/15/2022	3
shirts	short sleeve shirt	4/16/2022	6
shirts	tang top	5/16/2022	4
shirts	long sleeve shirt	6/1/2022	3
shirts	short sleeve shirt	7/1/2022	10
shirts	tang top	7/2/2022	12
shirts	long sleeve shirt	4/15/2023	5
shirts	short sleeve shirt	4/16/2023	30
shirts	tang top	5/16/2023	7
shirts	long sleeve shirt	6/1/2023	8
shirts	short sleeve shirt	7/1/2023	9
shirts	tang top	7/2/2023	8
pants	jeans	4/15/2022	57
pants	shorts	4/16/2022	6
pants	leggings	5/16/2022	4
pants	jeans	6/1/2022	3
pants	shorts	7/1/2022	10
pants	leggings	7/2/2022	12
pants	jeans	4/15/2023	5
pants	shorts	4/16/2023	20
pants	leggings	5/16/2023	7
pants	jeans	6/1/2023	8
pants	shorts	7/1/2023	19
pants	leggings	7/2/2023	8

Expected outcome:

[lbendlin · Accepted solution]

How do you prevent the user from selecting a range that reaches across years?  What should happen if they select 2022-10-01 to 2023-02-28  ?

See attached for a standard approach using SAMEPERIODLASTYEAR

[wilberk]

The desired result if they select 2022-10-01 to 2023-02-28 would be for the previous year column to show 2021-10-01 to 2022-02-28

[lbendlin]

Good - that's what my proposal based on SAMEPERIODLASTYEAR does.