Forum Discussion

AsNa_92's avatar
AsNa_92
Resolver II
2 years ago
Solved

Create Period slicer based on Date

Hi Guys

 

I have Date column and I want to create slicer with 1D/ 1W/ 1M/ 3M/ 6M and All.

Can you please assist me on how to elaborate that?

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi AsNa_92 - you can add a calculated column to your Date table that categorizes each date based on the defined periods (1D, 1W, 1M, 3M, 6M, and All).

    Period =
    VAR CurrentDate = MAX('Datetable_W'[Date])
    RETURN
    SWITCH(
        TRUE(),
        'Datetable_W'[Date] >= CurrentDate - 1, "1D",
        'Datetable_W'[Date] >= CurrentDate - 7, "1W",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -1), "1M",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -3), "3M",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -6), "6M",
        "All"
    )

     

     

    i have added the above column in my visual with slicer chart and it looks as expected from above snap

     

     

     

    Hope this helps

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi AsNa_92 - you can add a calculated column to your Date table that categorizes each date based on the defined periods (1D, 1W, 1M, 3M, 6M, and All).

    Period =
    VAR CurrentDate = MAX('Datetable_W'[Date])
    RETURN
    SWITCH(
        TRUE(),
        'Datetable_W'[Date] >= CurrentDate - 1, "1D",
        'Datetable_W'[Date] >= CurrentDate - 7, "1W",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -1), "1M",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -3), "3M",
        'Datetable_W'[Date] >= EDATE(CurrentDate, -6), "6M",
        "All"
    )

     

     

    i have added the above column in my visual with slicer chart and it looks as expected from above snap

     

     

     

    Hope this helps

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  AsNa_92 ,

     

    Is your problem solved? If it is solved, you can mark the correct answer as a marker, if not, provide more detailed information and we can help you better!

     

    Best Regards,

    Liu Yang

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