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

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.

slicer.pbix13.1 MB