Forum Discussion

AsNa_92's avatar
AsNa_92
Icon for Resolver II rankResolver 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

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper 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