Need help to create slicer which can help user to select last two months ,three months , four months

[Truelearner]

i have support tickets data with me and the client wants a filter with below functionality 

when user selects current month - it has to filter dashboard to current month 

when user selects last two months - it has to filter dashboard to last two months 

when user selects last three months - it has to filter dashboard to last three months 

when user selects last four months - it has to filter dashboard to last four months 

@mwegener , @amitchandak , @az38 , @Jimmy801 , @JarroVGIT 

[amitchandak]

How to get such a slicer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

How to get these measures, first prefer to have a date dimension table joined with your fact date

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

Rolling 2 months = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-1,MONTH))  
or
Rolling 2 months = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxx(Sales,Sales[Sales Date]),-1,MONTH)) 
or
Rolling 2 months = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxx(date,Date[ Date]),-1,MONTH)) 

Rolling 3 months = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))  
Rolling 4 months = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-4,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Truelearner]

@amitchandak i am not looking for measure calulcation . I am looking for a column which would have values like 

current month

last two months

last three months

last four months and so on ... when people select any of the above whole report has to filter accordingly 

[amitchandak · Accepted solution]

have you check this

"How to get such a slicer :https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50..."

In my last update

[mwegener]

Hi @Truelearner ,

look at this.

https://dax.tips/2020/02/05/overriding-slicers/

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @Truelearner ,

look at this.

https://docs.microsoft.com/en-us/power-bi/desktop-grouping-and-binning

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.