Slicer with date entries of a table with special formatting

[MakZH]

Dear community,

I would like to create a slicer which shows all available date entries of a table to be able to filter a report.
For the dates, which are exactly at the end of the previous month, the end of the previous quarter and at the end of the previous year, the date should be replaced with a specific text.

For example:
+------------------+
| Prev Quarter End |
| Prev Month End |
| 2023-07-18 |
| 2023-07-17 |
| 2023-07-16 |
| 2023-07-15 |
.
.
.

Unfortunately with the current measure I only get one date.

VAR _Date = SELECTEDVALUE(Table[DATE])
 Return
 SWITCH(
     TRUE(),
     _Date = ENDOFQUARTER (PREVIOUSQUARTER ( Table[DATE] )), "Prev Quarter End",
     _Date = ENDOFMONTH( PREVIOUSMONTH( Table[DATE] )), "Prev Month End",
     FORMAT(_Date,"yyyy-mm-dd")
 )

Thank you for your tips

[AlanFredes · Accepted solution]

Hi MakZH,

I would build a calculated column based on the DATE column and TODAY() date.

Slicer Date =

VAR ENDOFMONTH_PREV = CALCULATE(

                        ENDOFMONTH( PREVIOUSMONTH(Table[DATE])),

                        FILTER(Dates,Dates[Dates]=TODAY())

                    )  

VAR ENDOFQUARTER_PREV = CALCULATE(

                        ENDOFQUARTER( PREVIOUSQUARTER (Table[DATE])),

                        FILTER(Table,Table[DATE]]=TODAY())

                    )

RETURN

 SWITCH(

     TRUE(),

     Table[DATE]=ENDOFMONTH_PREV, "Prev Month End",

     Table[DATE] = ENDOFQUARTER_PREV, "Prev Quarter End",

     FORMAT(Table[DATE],"yyyy-mm-dd")

 )

Given that End Date for the previous Month and the Previous Quarter are the same (30-Jun-23) I left "Prev Month End" as the priority value.

It would look like this:

Did I answer your question? Mark my post as a solution!

[AlanFredes · Accepted solution]

Hi MakZH,

I would build a calculated column based on the DATE column and TODAY() date.

Slicer Date =

VAR ENDOFMONTH_PREV = CALCULATE(

                        ENDOFMONTH( PREVIOUSMONTH(Table[DATE])),

                        FILTER(Dates,Dates[Dates]=TODAY())

                    )  

VAR ENDOFQUARTER_PREV = CALCULATE(

                        ENDOFQUARTER( PREVIOUSQUARTER (Table[DATE])),

                        FILTER(Table,Table[DATE]]=TODAY())

                    )

RETURN

 SWITCH(

     TRUE(),

     Table[DATE]=ENDOFMONTH_PREV, "Prev Month End",

     Table[DATE] = ENDOFQUARTER_PREV, "Prev Quarter End",

     FORMAT(Table[DATE],"yyyy-mm-dd")

 )

Given that End Date for the previous Month and the Previous Quarter are the same (30-Jun-23) I left "Prev Month End" as the priority value.

It would look like this:

Did I answer your question? Mark my post as a solution!

[MakZH · Accepted solution]

Hi AlanFredes,

Thank you for your proposal. It works!

I missed to say that in the case above a specific range of dates are used, so the latest date should maybe calculated with "MAX(Table[Date])".

Slicer Date =

VAR ENDOFMONTH_PREV = 
    CALCULATE(
        ENDOFMONTH( PREVIOUSMONTH(Table[Date])),
        FILTER(Table, Table[Date] = MAX(Table[Date]))
    )  

VAR ENDOFQUARTER_PREV = 
     CALCULATE(
        ENDOFQUARTER( PREVIOUSQUARTER (Table[Date])),
        FILTER(Table, Table[Date] = MAX(Table[Date]))
     )

RETURN

SWITCH(
     TRUE(),
     Table[Date] = ENDOFMONTH_PREV, "Prev Month End",
     Table[Date] = ENDOFQUARTER_PREV, "Prev Quarter End",
     FORMAT(Table[Date], "yyyy-mm-dd")
)