Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

X-axis for comparing custom date ranges

Hi all,   For a client of mine I want to create a line graph which shows a custom date comparison. I have a date slicer coming from an unlinked table, which the user can use to choose a start- and ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , try a trailing period measure like this with date table

     

    previous period =
    var _min = minx(allselected('Date'), 'Date'[Date])
    var _max = maxx(allselected('Date'), 'Date'[Date])
    diff = (datediff(_min,_max,day)+1)*-1
    return
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],diff,Day))

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , try a trailing period measure like this with date table

 

previous period =
var _min = minx(allselected('Date'), 'Date'[Date])
var _max = maxx(allselected('Date'), 'Date'[Date])
diff = (datediff(_min,_max,day)+1)*-1
return
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],diff,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks amitchandak! That did the trick 🙂