Slicer for Last 6 Months

[hello_MTC]

 Hello,

I want to create a sclicer for "Last 6 Months", "Last 3 Months". I've columns ready as below. I need to submit this in 24hrs. Help will be appreciated.

[v-eqin-msft · Accepted solution]

Hi @hello_MTC ,

1. Create a table for slicer:

2. Add a flag measure:

Flag = 
var _diff= DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

3.Apply it to visual-level filter pane:

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

[v-eqin-msft · Accepted solution]

Hi @hello_MTC ,

So it depends on which date you want to be based on.

For example: you could replace TODAY() with MAXX(ALL('Table'),[Date]).

Table = CALENDAR(DATE(2021,9,1),DATE(2022,4,30))

Flag = 
var _maxDate=MAXX(ALL('Table'[Date]),[Date])  // based on the lateset date in Table
var _diff= DATEDIFF(MAX('Table'[Date]),_maxDate,DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

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

[hello_MTC]

It worked and I Accepted as Solution. But something is missing here. The Slicer is not working because it doesn't have any relationship with my other table.
?

[v-eqin-msft · Accepted solution]

Hi @hello_MTC ,

1. Create a table for slicer:

2. Add a flag measure:

Flag = 
var _diff= DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

3.Apply it to visual-level filter pane:

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

[hello_MTC]

Hi, this is great but i'm facing small issue here.

Whenever I select filter = 1 in filter panel it doesn't show me data of 2021.

[v-eqin-msft]

Hi @hello_MTC ,

Because my measure is based on Today(2022/July) , so for last 6 months, the minimum  month will be 2022/February

Best Regards,
Eyelyn Qin

[hello_MTC]

Ok I got it but is there any way to solve this?

[KT_Bsmart2gethe]

Hi @hello_MTC ,

I forgot to mentioned if it is in Power BI, you can simply untick the items from the filter and it will not appear in the slicer. 

[KT_Bsmart2gethe]

Hi @hello_MTC ,

Add two columns with in Power Query or Power Pivot by using if formula:

if date is less (than today's date - 90 days / 180 days) then 3 months / 6 months then null / ""

Add slicer with newly added column then go to settings, tick hide item with no data,

Regards

KT 

[hello_MTC]

What If I want only Last 6 Months from current date only. Remove Last 3 Months

[hello_MTC]

However, I wrote this 

LastMonths = IF('uat_db incident_condition_template'[updated_at]<TODAY()-90,"Last 3 Months",IF('uat_db incident_condition_template'[updated_at]<TODAY()-180,"Last 6 Months"))

And I have Jan, Feb, Mar and Apr data in this column. I got this.

Is it True?

[hello_MTC]

Thank you for your reply. it is highly appreciated

Can you please write a proper DAX function here. It will be more helpful.

[KT_Bsmart2gethe]

Hi @hello_MTC ,

Would you kindly share some sample data with sensitive information removed? I will get back to you with the formula. It does help to have your question resolved quicker.

Regards

KT

[hello_MTC]

send me your email id.