X-axis for comparing custom date ranges

[Skoltn]

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: 

Current Period = CALCULATE([FunnelAmount], DATESBETWEEN(Facttable[_DateID], [CustStartCurrentPeriod], [CustEndCurrentPeriod]))
Previous Period = CALCULATE([FunnelAmount], DATESBETWEEN(Facttable[_DateID], [CustStartPreviousPeriod], [CustEndPreviousPeriod]))

The [FunnelAmount] measure is a simple SUM() and the [Cust...] measures represent dates.

[amitchandak · Accepted solution]

@Skoltn , 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.

[Skoltn]

Thanks @amitchandak! That did the trick 🙂