Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Advanced Slicer filtering months

Hello Folks ! Please help me with the following doubt !

 

I have a slicer which filters Months. I want the output on barchart such as :- If I select June, Bar chart should show me the Month - March, April, May, June, July, Aug, Sept, December, June Previous Year and December Previous Year.

 

Thank you in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You can create a seperate table with month name, then create the specified measure like

    Measure = 
IF (
    ISFILTERED ( MonthName[Month] ),
    SWITCH (
        SELECTEDVALUE ( MonthName[Month] ),
        "June",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    (
                        [Date] < DATE ( YEAR ( TODAY () ), 10, 1 )
                            && [Date] >= DATE ( YEAR ( TODAY () ), 3, 1 )
                    )
                        || (
                            [Date] >= DATE ( YEAR ( TODAY () ), 12, 1 )
                                && [Date] <= DATE ( YEAR ( TODAY () ), 12, 31 )
                        )
                        || (
                            [Date]
                                >= DATE ( YEAR ( TODAY () ) - 1, 6, 1 )
                                && [Date]
                                    <= DATE ( YEAR ( TODAY () ) - 1, 6, 30 )
                        )
                        || (
                            [Date]
                                >= DATE ( YEAR ( TODAY () ) - 1, 12, 1 )
                                && [Date]
                                    <= DATE ( YEAR ( TODAY () ) - 1, 12, 31 )
                        )
                )
            )
    ),
    SUM ( 'Table'[Value] )
)

     

     

    Best Regards,

    Stephen Tao

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    You can create a seperate table with month name, then create the specified measure like

    Measure = 
IF (
    ISFILTERED ( MonthName[Month] ),
    SWITCH (
        SELECTEDVALUE ( MonthName[Month] ),
        "June",
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    (
                        [Date] < DATE ( YEAR ( TODAY () ), 10, 1 )
                            && [Date] >= DATE ( YEAR ( TODAY () ), 3, 1 )
                    )
                        || (
                            [Date] >= DATE ( YEAR ( TODAY () ), 12, 1 )
                                && [Date] <= DATE ( YEAR ( TODAY () ), 12, 31 )
                        )
                        || (
                            [Date]
                                >= DATE ( YEAR ( TODAY () ) - 1, 6, 1 )
                                && [Date]
                                    <= DATE ( YEAR ( TODAY () ) - 1, 6, 30 )
                        )
                        || (
                            [Date]
                                >= DATE ( YEAR ( TODAY () ) - 1, 12, 1 )
                                && [Date]
                                    <= DATE ( YEAR ( TODAY () ) - 1, 12, 31 )
                        )
                )
            )
    ),
    SUM ( 'Table'[Value] )
)

     

     

    Best Regards,

    Stephen Tao

     

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