Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi,
I have a requirement to create slicer with custome date values.
The values are
All
Current Quarter
Last Quarter
Last 2 Quarters
So, the visuals on the report needs to show the data as per above date slicer value selection.
Ex: if i select 'Last Quarter' report need to show data for last quarter only.
I have a Date table and Fact table with date columns and relationship between them.
Please can help how can i achieve this.
Thanks
Solved! Go to Solution.
Hi @srk_powerbi ,
You could follow these steps:
1. Create a single table for Slicer like this:
2. Use the following formula after adding a Quarter column:
Measure =
VAR _sele =
SELECTEDVALUE ( forSlicer[Type] )
RETURN
SWITCH (
_sele,
"ALL", 1,
"Current Quarter", IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ), 1, 0 ),
"Last 2 Quarters",
IF ( QUARTER ( TODAY () ) - MAX ( 'Date'[Quarter] ) >= 1, 1, 0 ),
"Last Quarter",
IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ) - 1, 1, 0 )
)
3. Apply the measure to filter pane (set as "=1") . My final output looks like this:
Please take a look at the pbix file here.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @srk_powerbi ,
You could follow these steps:
1. Create a single table for Slicer like this:
2. Use the following formula after adding a Quarter column:
Measure =
VAR _sele =
SELECTEDVALUE ( forSlicer[Type] )
RETURN
SWITCH (
_sele,
"ALL", 1,
"Current Quarter", IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ), 1, 0 ),
"Last 2 Quarters",
IF ( QUARTER ( TODAY () ) - MAX ( 'Date'[Quarter] ) >= 1, 1, 0 ),
"Last Quarter",
IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ) - 1, 1, 0 )
)
3. Apply the measure to filter pane (set as "=1") . My final output looks like this:
Please take a look at the pbix file here.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @srk_powerbi,
you can use the disconnected dimension pattern for this.
create a table that contains the static values
Current Quarter, Last Quarter, Last Two Quarter.
Then you would create a measure that looked something like
disconnected value = if hasonevalue('table'[label]),
switch(values('table'[label]),
"Current Quarter", calculate(sum(table[something]), filter(dates, dates[lastquarter] = true())),
......
)
you will need a date table which includes columns that identify if the date ranges are true or false. If you want to allow the user to define the end date then you would use the selected date and time intelligence functions to find the appropriate period.
Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Hi @srk_powerbi
You can add a column to your date table classifying the quarters:
Current | 2020Q4 |
Last | 2020Q3 |
Last 2 | 2020Q3 |
Last 2 |
2020Q2 |
Other |
All the other quarters |
and use that column as slicer. The all option would just be no selection on the slicer
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB thanks for replying.
But, how do we add two values
lest say for 2020Q4 it comes under 'Current Quarter' and 'Last 2 Quarters' ? we can assign one value to one row in calender table right?
User | Count |
---|---|
90 | |
88 | |
85 | |
80 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |