Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi Anonymous ,


    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's avatar
HarishKM
Super User
5 years ago

Anonymous 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 .