Show last 3 months from selected month in Power BI

Question

I have a table that contains four columns months (Format: mmm-yy), staffs, sales, expenses. I want to create three bar graphs for staff, sales, and expenses with the month in the axis for each bar graph. I want to create a month filter so that if I select a particular month data of that month and previous two months should be shown in the graph.

When I select Aug 17 in the filter than data of only Jun-17, July-17 and Aug-17 should be shown in the bar graph

Solution

I don't know if this is the best way to do this, but I can get it working as follows (I will assume your table name is Sales):

1. Create a separate table to use as your slicer. Modeling > New Table

---

SlicerTable = VALUES(Sales[MonthofYear])

---

2. Create a new measure that returns blank for the months you don't want.

---

3MonthSales = 
    VAR SelectedDate = SELECTEDVALUE(SlicerTable[MonthofYear])
    VAR CurrentDate = SELECTEDVALUE(Sales[MonthofYear])
    RETURN IF(CurrentDate <= SelectedDate &&
              CurrentDate >  EOMONTH(SelectedDate,-3),
              SUM(Sales[Sales]), BLANK())

---

3. Create a slicer on SlicerTable[MonthofYear] and a bar chart on Sales[MonthofYear] and Sales[3MonthSales] and you should get something like this:

Comments

[StackTrace]

I'm trying to implement this functionality but bar chart is only showing one month - the selected one, what could i be doing wrong?

[Alexis Olson]

Is your slicer table a separate unrelated table like I specified?

[StackTrace]

Got it working now, it was a date format mismatch between my slicer table & the main table