Forum Discussion
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 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.
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 tableForslicer1 = 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.
4 Replies
Anonymous , 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/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYMEThanks 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
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 .
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 tableForslicer1 = 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.
