Creating slicer to filter measure

[NJ81858]

Hello,

I have a measure that calculates the amount of change in total sales for each sales person between the first date and the most recent date, and I would like to create a slicer to filter this. My measure is calculated like this:

Amount of Change = 
VAR _end = LASTDATE('Sales'[Date])
VAR _start = FIRSTDATE('Sales'[Date])
VAR _startnum = CALCULATE(SUM('Sales'[Total Sales]), FILTER('Sales', [Date] = _start))
VAR _endnum = CALCULATE(SUM('Sales'[Total Sales]), FILTER('Sales', [Date] = _end))

RETURN
_endnum - _startnum

I also have a date slicer so that the end user can filter to specific dates as needed and see the amount of change.

Thank you in advance!

[khaledHabib90]

You will probably need 2 separate date tables to use in the slicer, one for the _Enddate another for the _Startdate

the 2 tables shouldn't have any relationship with your sales table

your Variables should look something like this

Amount of Change = 
VAR _end = SELECTEDVALUE('Yourdatecolumn in the end date table')
VAR _start = SELECTEDVALUE('Yourdatecolumn in the start date table')
VAR _startnum = CALCULATE(SUM('Sales'[Total Sales]), FILTER('Sales', [Date] = _start))
VAR _endnum = CALCULATE(SUM('Sales'[Total Sales]), FILTER('Sales', [Date] = _end))

RETURN
_endnum - _startnum

If I answered your question please accept this reply as the solution 🙂