Offset current month selection across multiple months in the future

[Anonymous]

Hi guys,

This is my first post on here so will try to be as clear as i can. I am new to PowerBi Desktop and have basic knowledge of how it works.
My current problem is as follows:

I have a sales dataset that includes contract start date, contract termination date, customer ID and customer Name.

I would like to create a filter that allows me to view contracts that are expiring in current and next 2 months to be able to target those customers.

I understand I could use a date filter with start to end date but would like something more "elegant". My aim is to have a date drop down selection e.g.
If I select February from the drop down many, i would like to see a list of contracts ending in February March and April.
If I select March, I would like to see a list to contracts ending in March, April and May.

The issue is that the drop down only filters on current month and I would like to somehow offset it to include the following 2 months in the current selection aswell.

Any help or advice would greatly be appreciated.

Thanks in advance.

[v-xicai]

Hi @Anonymous ,

Based on my test , it is can't implement your desired output completely. To work around this issue, you can try my solution following steps below.

Firstly, you can create calculated table DateDim ,then create column Month as the date month slicer.

DateDim = CALENDARAUTO()

Month = MONTH('DateDim'[Date])

Then create measure Filter1 in your data table(assuming table1), put measure Filter1 in the Visual Level Filter of goal table visual, and setting the Filter1 as "is not blank", don't forget click "Apply filter" button.

Filter1 = IF(CALCULATE(MIN('DateDim'[Date]),ALLSELECTED('DateDim'[Month]))<MAX(Table1[termination date])&&MAX(Table1[termination date])<=CALCULATE(MAX('DateDim'[Date]),ALLSELECTED('DateDim'[Month])),1,BLANK())

Finally ,set the date month slicer as "Between" but not "Dropdown", every time you type start month, then type the end month in value start month +2, for example the picture below.

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaN_3HJ0wMdBr8DFTcV-Vb0B2aNkfWW_FmZ6ax-mmvENwA?e=fidvDO

Best Regards,

Amy

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

[Anonymous]

Hi @v-xicai ,

Thank you for your reply and solution. 

I have tried to implement the solution and unfortunately its not exactly what I was looking for. 

My aim is to not have a slicer but a drop down menu which automatically sellects current and the next x months from a single user selection.

Are there any solutions to the problems statement?

Thanks

[v-xicai]

Hi @Anonymous ,

If my reply help your issue, so kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai · Accepted solution]

Hi @Anonymous ,

Yes , I know you need dropdown menu, while I tested and referred, find it is not supported to implement your desired output in Power BI Desktop currently, so I offer you this clumsy alternative solution.

While I have to agree that your demand is a good idea, so you can post your new idea https://ideas.powerbi.com/forums/265200-power-bi-ideas , to improve the Power BI Desktop.

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Amy

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