Dynamic Filtering of X-Axis

[ryanbott]

Hey there,

I'm looking to dynamically filter my visual with two slicers. One slicer is tied to an unrelated Date table to get access to all of the Month Year Date values in the data. The other slicer is tied to a table to pick between 1M, 3M, 6M, and 1Y periods. The effect I'd like to see is, for example, select June 2020 and 6M and then display the trailing 6 month period behind June 2020. I've been trying a handful of different things, and the closest was probably a new Calculated Table, but that won't support a Dynamic approach.

I had tried to create a measure that I would use on the y-axis, but it didn't seem to work out and I'm not exactly sure why.

MemberJoinedInTerm = 
VAR range = SELECTEDVALUE(MemberJoinedTimeChoice[TimeChoice])
VAR rangeMonth = SWITCH(range,
    "1M",
    -1,
    "3M",
    -3,
    "6M",
    -6,
    "1Y",
    -12
)
var startDateYear = YEAR(SELECTEDVALUE(Dates[Date]))
var startDate = DATE(startDateYear, 1 , 1)
var endDate = EOMONTH(EDATE(startDate, rangeMonth) ,0)
RETURN
CALCULATE(SUM(monthTable[New Members]), DATESINPERIOD(monthTable[Date].[Date], startDate, rangeMonth, MONTH))

ex. the visual that needs dynamic filtering & the two slicers that should provide the filtering inputs (start date & date period)

ex. the settings on the visual for its axis

ex. some of the sample data. 

ex. a cropped part of the data model

[v-lionel-msft · Accepted solution]

Hi @ryanbott ,

Please refer to the blog.

Display Last N Months & Selected Month using Single Date Dimension in Power BI 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryanbott]

Hi @Ashish_Mathur , unfortunately, I only have Excel for Mac and I'm unable to inspect your Excel solution to see if I can apply it in PowerBI.

[Ashish_Mathur]

Hi,

Download my MS Excel file and do not open it.  Open PowerBI Desktop and go to File > Import > Power Query, Power Pivot and Power View.  This will import all my work from the Excel file to the PBI Deskto file.  Thereafter, you will still have to open my Excel workbook to see which columns have to be dragged to which visual.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.