Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MakZH
Frequent Visitor

Slicer with date entries of a table with special formatting

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

2 ACCEPTED SOLUTIONS
AlanFredes
Resolver IV
Resolver IV

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:

AlanFredes_1-1689711884991.png

 

 

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

 

View solution in original post

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

View solution in original post

2 REPLIES 2
AlanFredes
Resolver IV
Resolver IV

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:

AlanFredes_1-1689711884991.png

 

 

Did I answer your question? Mark my post as a 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")
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.