How to show last 13 months in X axis based on single slicer selection?

1
votes

EDIT: As this is an old question, the intended solution is supported by the relative date slicer in Power BI as seen here.

I am building a rolling 13 month net revenue chart and I've came across a requirement to show the last 13 month in x axis based in what month (YYYYMM) the user selected in a YYYYMM slicer.

The chart below is the one I want:

In order to get the intended behaviour, currently I need to select each one of the 13 month in YYYYMM slicer:

Any ideas on how I can get this to work in Power BI so that the user just have to select the base month?

Thanks in advance, Cristhian.

1
data-visualizationpowerbi

1 Answers

0
votes

Let's say you have a table data(date, value).

Add a table datefilter(date) with dates you want to select. Add a slicer by this date.

Add a calculated column to data : date2 = DATEADD('data'[date].[Date],13,MONTH)

Add this measure: 

current = IF(AND(
  MAX('data'[date]) < MAX('datefilter'[date]),
  MAX('data'[date2]) > MAX('datefilter'[date])),1,0)

filter your visualisation by current is 1.

enter image description here