Slicer

[Krutigawale33]

Hello All,

May be silmilar thing I have posted earlier. I have requirement to create slicer which are having two fields:-

1)Fiscal YTD

2)Rolling 12 Months

When enduser select "Fiscal YTD" the graph should show the months on x-axis till date/selected Month.

when end user select "Rolling 12 Months". the x-axis should show months 12 months rolling/Back from current/selected month.

The page is also having another slicer on Fiscal month to selected the month.

Fiscal month is column from Table.

How to implement slicer with two above fields to work accordingly.

[v-yalanwu-msft · Accepted solution]

Hi @Krutigawale33 ,

According to your description, you could create two tables as slicers, then create a flag measure and apply it into filter. the following formula to create :

Step1: Enter slicer1 table

Forslicer1 = DISTINCT(SELECTCOLUMNS('W_MCAL_PERIOD_D',"month",[MCAL_PER_NAME_MNTH]))

Step2: Enter slicer1 table

Step3: Create a flag measure

flag =
VAR _sele =
MAX ( 'Forslicer1'[month] )
VAR _date =
DATE ( RIGHT ( _sele, 4 ), MONTH ( LEFT ( _sele, 3 ) & " 1" ), 1 )
VAR _actual =
MAX ( 'W_MCAL_PERIOD_D'[MCAL_PER_NAME_MNTH] )
VAR _actualDate =
DATE ( RIGHT ( _actual, 4 ), IF ( _actual <> BLANK (), MONTH ( LEFT ( _actual, 3 ) & " 1" ) ), 1 )
VAR _datediff =
DATEDIFF ( _date, _actualDate, MONTH )
RETURN
SWITCH (
SELECTEDVALUE ( 'Forslicer2'[Slicer] ),
"Fiscal YTD",
IF (
RIGHT ( _actual, 4 ) = SELECTEDVALUE ( 'Forslicer1'[Year] )
&& _actualDate <= _date,
1,
0
),
"Rolling 12 Months",
IF ( _datediff <= 12 && _datediff >= 0, 1, 0 )
)

Step4: Apply the flag measure into filter

Step5: the final output is shown below

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HarishKM]

@Krutigawale33 Hey 

Create a disconnected table then drag that value to a slicer .

you can use swtich function .

test = switch( true(),
 selectedvalue([tablename[name]) = "Fiscal YTD",[Fiscal YTD],
selectedvalue([tablename[name]) = "Rolling 12 Months",[Rolling 12 Months],
,[Rolling 12 Months],)

then drag that measure to your chart and matrix .

whenever you make slicer selection as fy ytd then it will fy ytd only .

[amitchandak]

@Krutigawale33 , You have to use Independently dateable in case you need to change the axis.

refer my video for that-  Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://www.youtube.com/watch?v=vlnx7QUVYME

[Krutigawale33]

Thanks @amitchandak 

I have tried with what you suggested but no luck

I am sharing my powerBI file https://drive.google.com/file/d/1gb6VTwCaBeOPxuZUqukmhS8dgZAXHBC9/view?usp=sharing