cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WaqarArshad Frequent Visitor
Frequent Visitor

Offset current month selection across multiple months in the future

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-xicai Super Contributor
Super Contributor

Re: Offset current month selection across multiple months in the future

Hi @WaqarArshad ,

 

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.

 

4 REPLIES 4
Highlighted
v-xicai Super Contributor
Super Contributor

Re: Offset current month selection across multiple months in the future

Hi @WaqarArshad ,

 

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())

 

7.png

 

 

 

 

 

 

 

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.

 

1.png

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaN_3HJ0wMdBr8DFTc...

 

Best Regards,

Amy

 

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

 

WaqarArshad Frequent Visitor
Frequent Visitor

Re: Offset current month selection across multiple months in the future

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 Super Contributor
Super Contributor

Re: Offset current month selection across multiple months in the future

Hi @WaqarArshad ,

 

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.

 

v-xicai Super Contributor
Super Contributor

Re: Offset current month selection across multiple months in the future

Hi @WaqarArshad ,

 

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