Forum Discussion
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 enddate of the current period. Then, I automatically calculate the start- and enddate of the previous period. E.g. when they choose 07/10/2020 - 15/11/2020 as current period, the previous period automatically becomes 28/08/2020 - 06/10/2020. The user can choose whatever period they want, two days, a whole year, does not matter.
Calculating the sum of sales/clicks/views for these two periods is no big deal. I already have KPI visuals with them. However, I also want to create a line graph that compares the two periods. The problem is, that I cannot get my x-axis right. Does anybody have any ideas on how to create an x-axis that compares both periods?
What I'm trying to achieve is this:
My measures are:
Previous Period = CALCULATE([FunnelAmount], DATESBETWEEN(Facttable[_DateID], [CustStartPreviousPeriod], [CustEndPreviousPeriod]))
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.
2 Replies
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.