Forum Discussion

rock_stage_user's avatar
rock_stage_user
Helper I
3 years ago
Solved

Custom date filter

can we edit date filter manually in power bi or is there any visual so we can add year month and date manually. in slicer date filter i am not able to edit on after add report on to the server

 

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    3 years ago

    Hi rock_stage_user ,

     

    You can try formula like below:

    Filtered Value = 
SWITCH (
    SELECTEDVALUE ( 'Table'[Custom_Filter] ),
    "Last Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER (
                'Table (2)',
                MAX ( 'Table (2)'[Date].[Year] )
                    = ( YEAR ( TODAY () ) - 1 )
            )
        ),
    "This Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER ( 'Table (2)', MAX ( 'Table (2)'[Date].[Year] ) = ( YEAR ( TODAY () ) ) )
        ),
    "Next Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER (
                'Table (2)',
                MAX ( 'Table (2)'[Date].[Year] )
                    = ( YEAR ( TODAY () ) + 1 )
            )
        )
)

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Set up a date table, marked as a date table, and you can then use the Year, Month, Day etc columns from the date table in separate slicers.

    • rock_stage_user's avatar
      rock_stage_user
      Helper I
      3 years ago

      in that case user will need to click multiple times need to edit in this filter only

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    Hi rock_stage_user ,

     

    You can try formula like below:

    Filtered Value = 
SWITCH (
    SELECTEDVALUE ( 'Table'[Custom_Filter] ),
    "Last Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER (
                'Table (2)',
                MAX ( 'Table (2)'[Date].[Year] )
                    = ( YEAR ( TODAY () ) - 1 )
            )
        ),
    "This Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER ( 'Table (2)', MAX ( 'Table (2)'[Date].[Year] ) = ( YEAR ( TODAY () ) ) )
        ),
    "Next Year",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER (
                'Table (2)',
                MAX ( 'Table (2)'[Date].[Year] )
                    = ( YEAR ( TODAY () ) + 1 )
            )
        )
)

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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