Filtering table based on a value from a slicer

[pcaslavka]

Hi there,

I have a dashboard the is used to forecast sales based on opportunities from Microsoft Dynamics CRM.

The user has the following requirement - he wants to choose the forecast start month on a slicer and the table should filter the records for the 3 months following the the start date (chosen month is included).

So for example if her chooses January 2017, he should see records from January, February and March of 2017. It always needs to be set to 3 month, never 2, never 4 etc. It is basically always 3 months.

Any idea on how to accomplish that in Power BI?

[pcaslavka · Accepted solution]

Great solution, like it a lot. Is there a way to order the months in the forecast start slicer? January followed by february etc.?

[pcaslavka]

Here is the sample file

Sample PBIX

The expected behavior is that when I choose January 2017 start date, I should see projects 1, 2, 3, 4 and 5 as those fall into January, February and March. Project 6 is April so it would not appear in the table. 

Thanks,

Petr

[Ashish_Mathur]

Hi,

You may refer to my solution here.  I have had to create a calendar table and then create 2 relationships - one from Table1 to the calendar table and another from Table3 to the calendar table.  I then rewrote your mesaure as well.

Hope this helps.

[pcaslavka · Accepted solution]

Great solution, like it a lot. Is there a way to order the months in the forecast start slicer? January followed by february etc.?

[Ashish_Mathur · Accepted solution]

You are welcone.  If my reply helped, please mark it as Answer.  I would suggest the approach of having 2 slicers - one in which you select the year and another in which you select the month.  Create a month_order table and build a relationshop from the month column in the calendar table to the month column of the month_order table.  Using the RELATED() function bring over the order column from the month_order table to the calendar table.  Then use the Sort by column feature.

Download the file from here.

[Ashish_Mathur]

Hi,

Share the download link of you Excel/PBIX file.  Also, show your expected result.