topic Slicer with date entries of a table with special formatting in DAX Commands and Tips

Slicer with date entries of a table with special formatting

MakZH — Tue, 18 Jul 2023 16:03:13 GMT

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

Re: Slicer with date entries of a table with special formatting

AlanFredes — Tue, 18 Jul 2023 20:25:02 GMT

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!

Re: Slicer with date entries of a table with special formatting

MakZH — Wed, 19 Jul 2023 09:11:36 GMT

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") )