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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Timeperiod slicer

Hi Folks ..

I want to create a slicer where the data is sorted by Year,Month,Week,Day wise so there is a default visual in Power BI Iused but the issue is I just want past and present data to be filtered but <refer the img below>

sailochana_0-1701328729986.pngsailochana_1-1701328775659.png

so here i want to control the slicer to show only last and this options excluding next .

How to acheive this or any other possible ways please kindly suggest .

 

Thanks .

@LivioLanzo  @Saud1999 @saud968 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @Anonymous 
There is no option to limit selections in the generic slicer.

You can create a slicer with similar functionality manually .
Please refer to the linked tutorials :

https://www.youtube.com/watch?app=desktop&v=3SmHtNf0QQ0

https://www.youtube.com/watch?v=fKygF7VEJnQ

https://www.youtube.com/watch?v=JLupF1j49wo

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

9 REPLIES 9
saud968
Continued Contributor
Continued Contributor

Create a New Table:

Go to the "Model" view in Power BI Desktop.
Create a new table by clicking on "New Table" in the "Model" ribbon.
Define the Table with Slicer Options:

Create a table that includes the following values: "Last Month" and "Present Month."
DAX

SlicerTable = UNION(
ROW("DateRange", "Last Month"),
ROW("DateRange", "Present Month")
)
Create a Relationship:

Ensure that there is a relationship between the new "DateRange" column in your slicer table and the appropriate column in your main data table (e.g., date column).
Create a Slicer Visualization:

Drag the "Slicer" visualization onto your report canvas.
Drag the "DateRange" column from your slicer table into the "Field" well of the slicer.
Filter Data with DAX:

Create a measure that uses DAX to filter your data based on the selected value in the slicer.
DAX

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
"Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
)
Replace 'YourData' with the actual name of your main data table. 

Let me know if this helps. 

If I have posted a response that resolves your question, please accept it as a solution to close the post.

Anonymous
Not applicable

Thanks for the reply ,

And I apologise for asking silly question , but I'm facing error  while creating measure 

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()) - 1 && 'YourData'[Date] < STARTOFMONTH(TODAY())),
"Present Month", CALCULATETABLE(YourData, 'YourData'[Date] >= STARTOFMONTH(TODAY()))
)

For this mesaure I got few errors which I tried to resolve but still found new errors 
Error : A function 'STARTOFMONTH' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I tried one more :

CurrentDate=Today()

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]) - 1 && 'CalendarTable'[Date] < STARTOFMONTH(CalendarTable[CurrentDate]))
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, 'CalendarTable'[Date] >= STARTOFMONTH(CalendarTable[CurrentDate]))
)
)

Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Even this also I tried :

StartOfMonth = STARTOFMONTH(CalendarTable[Date])

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth] - 1 && [Date] < [StartOfMonth])
),
"Present Month", CALCULATETABLE(
FILTER(CalendarTable, [Date] >= [StartOfMonth])
)
)

Error :The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Can u please suggest me more ...??

Thanks .

saud968
Continued Contributor
Continued Contributor

No worries, I am also in a learning phase and not as expert as others but I will try to help you as much as possible. 

you have a table named 'CalendarTable' with a column 'Date' and a slicer table named 'SlicerTable' with a column 'DateRange,' you can create the following measures:

DAX

CurrentDate = TODAY()

FilteredData =
SWITCH (
SELECTEDVALUE('SlicerTable'[DateRange]),
"Last Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY()) - 1 &&
'CalendarTable'[Date] < STARTOFMONTH(TODAY())
)
),
"Present Month",
CALCULATETABLE(
YourData,
FILTER(
'CalendarTable',
'CalendarTable'[Date] >= STARTOFMONTH(TODAY())
)
)
)
Make sure to replace 'YourData' with the actual name of your main data table. This measure filters your data based on the selected value in the 'SlicerTable' slicer, considering the "Last Month" and "Present Month" options.

Note: The 'YourData' table should have a relationship with the 'CalendarTable' on the date column.

Anonymous
Not applicable

yeah I tried this, its not working . 

The error is in STARTOFMONTH() OR CALCULATETABLE() , I'm not getting. Why that STARTOFMONTH() is not accepting today() as parameter ,if we define it as varibale also it is raising same error as I mentioned above . I tried out all possible cases but still the error remains same . 

 

sailochana_0-1701421312213.png

 

sailochana_1-1701422350157.png

 

 

saud968
Continued Contributor
Continued Contributor

saud968
Continued Contributor
Continued Contributor

Will it be possible for you to share the PBIX file

Anonymous
Not applicable

Nope , Sorry .

Ritaf1983
Super User
Super User

Hi @Anonymous 
There is no option to limit selections in the generic slicer.

You can create a slicer with similar functionality manually .
Please refer to the linked tutorials :

https://www.youtube.com/watch?app=desktop&v=3SmHtNf0QQ0

https://www.youtube.com/watch?v=fKygF7VEJnQ

https://www.youtube.com/watch?v=JLupF1j49wo

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Anonymous
Not applicable

Thanks Bud , those videos helped to proceed in different way .

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors