Forum Discussion

NJ81858's avatar
NJ81858
Icon for Helper IV rankHelper IV
2 years ago

Creating slicer to filter measure

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!

Need Help

1 Reply

  • khaledHabib90's avatar
    khaledHabib90
    Icon for Resolver II rankResolver II
    2 years ago

    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 ğŸ™‚