Timeperiod slicer

[Anonymous]

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>

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 

[saud968]

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]

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]

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]

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 . 

[saud968]

Try following this video - https://www.youtube.com/watch?v=Su1mERlwNdM&t=233s

[saud968]

Will it be possible for you to share the PBIX file

[Anonymous]

Nope , Sorry .

[Ritaf1983 · Accepted solution]

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

[Anonymous]

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