Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
22 |