Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
Hi MakZH,
I would build a calculated column based on the DATE column and TODAY() date.
It would look like this:
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")
)
Hi MakZH,
I would build a calculated column based on the DATE column and TODAY() date.
It would look like this:
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")
)
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |