Show last 3 months from selected month in Power BI

2
votes

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

Graph

1
powerbidax

1 Answers

5
votes

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])
  1. 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())
  1. Create a slicer on SlicerTable[MonthofYear] and a bar chart on Sales[MonthofYear] and Sales[3MonthSales] and you should get something like this:

Bar Chart