Slicer With single selection

[ravitejaballa]

Hi,

I have a data like below,

where "Months Since Started" is months difference with current date => (TodayMonth - startDateMonth)

Now i want to have slicer on report which filters

1. Contract expirying with in 6 months => Shows Devices = A, C, D, E

2. Contract expirying with in 3 months => Shows Devices = A, D

3. Contract expirying with in 1 month => Shows Devices = E

Note: Slicer will be single selection

Device	Contract Start Date	Months Since Started
A	17-Dec-19	3
B	21-Sep-18	18
C	15-Oct-19	5
D	14-Jan-20	2
E	16-Sep-19	6

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ravitejaballa]

@dax @Ashish_Mathur @amitchandak 

Here is my sample data.

Device	Contract Start Date	Months Since Started
A	17-Dec-19	3
B	21-Sep-18	18
C	15-Oct-19	5
D	14-Jan-20	2
E	16-Sep-19	6

Index	Contract expirying
1	with in 6 months
2	with in 3 months
3	with in 1 month

So Slicer should filter table as below.

1. Contract expirying "with in 6 months" => Shows Devices = A, C, D, E

2. Contract expirying "with in 3 months" => Shows Devices = A, D

3. Contract expirying "with in 1 month" => Shows Devices = C, E

[dax]

Hi @ravitejaballa , 

Yes, as @Ashish_Mathur  mentioned , the"within 1 month" option should return blank(the date which within a month of today is 202/2/29-2020/3/31, no records within this range, it should return blank). Or you also could inform me your logic of  "within 1 month", then we will help you more correctly.

Best Regards,
Zoe Zhi

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

[ravitejaballa]

@Ashish_Mathur @dax 

To simplify further.

Same slicer but data should filter as shown.

ExpiryIn1Month =
IF(mtagData[contractDateDiff.months] == 6, TRUE())

ExpiryIn3Months =
IF(AND(mtagData[contractDateDiff.months] >= 1, mtagData[contractDateDiff.months] <= 3), TRUE())

ExpiryIn6Months =
IF(AND(mtagData[contractDateDiff.months] >= 1, mtagData[contractDateDiff.months] <= 6), TRUE())

[Ashish_Mathur]

Hi,

That is exactly what my solution does.  I have shared the file with you.

[ravitejaballa]

@Ashish_Mathur No, It is not working.

If you select "within 1 month" no data is shown.

[Ashish_Mathur]

Hi,

In the "Months since started" column, there is no entry of 1.  Therefore when you select "Within 1 month" in the slicer, no row shows up.

[dax]

Hi @ravitejaballa , 

I don't know your slicer style, you could refer to my sample for details. If this is not waht you want, please correct me and inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[amitchandak]

You can get this by rolling months

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6,MONTH))  
Or
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],Today(),-6,MONTH))  
//3
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,MONTH))  
Or
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],Today(),-3,MONTH))
//1
Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-1,MONTH))  
Or
Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],Today(),-1,MONTH))    

You can use a slicer like this to handle these measures

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

[ravitejaballa]

@amitchandaksorry It won't help. I already have computated data in "Months since started". I only need to have a slicer as described.

[amitchandak]

@ravitejaballa ,This did not help ?

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

Let me check