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
AndyDD_UK
Helper III
Helper III

Users select eg Last 7 days or Any dates they choose

I need the report page visuals to default to show date range for the current year 

In addition, I need the following

1) Users can select, as examples, 'Last 7 days' 'Last 14 days', 'Last 30 days'..

2) Or can select any date range from the last 2 years

 

How can do I do this ?

 

So far I've thought about using calendar table with a column working out whether it's YTD or not

To use the calendar table in a drop-down

To use a bookmark that is based on YTD

In addition to use a disconnected table 'Last 7 days'

 

In addition to have a disconnected table for 'Last 7 days', 'Last 14 days' etc' and to use this in a drop-down with a bookmark showing/hiding this

 

But don't think this is best option 

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
Community Support

Thanks @rajendraongole1  for his prompt reply. 
Hi @AndyDD_UK ,

Maybe you can use relative date slicer to solve your problem:

vyifanwmsft_0-1718003489873.png

Enter a number to define the relative date range.

vyifanwmsft_1-1718003521348.png

vyifanwmsft_2-1718003604714.png

vyifanwmsft_3-1718003617964.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yifanw-msft
Community Support
Community Support

Thanks @rajendraongole1  for his prompt reply. 
Hi @AndyDD_UK ,

Maybe you can use relative date slicer to solve your problem:

vyifanwmsft_0-1718003489873.png

Enter a number to define the relative date range.

vyifanwmsft_1-1718003521348.png

vyifanwmsft_2-1718003604714.png

vyifanwmsft_3-1718003617964.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rajendraongole1
Community Champion
Community Champion

Hi @AndyDD_UK - Create measures for IsInLast7Days, IsInLast14Days, IsInLast30Days, and IsInCurrentYear

 

IsInLast7Days =
IF (
AND (
[Date] >= TODAY() - 7,
[Date] <= TODAY()
), 1, 0
)

IsInLast14Days =
IF (
AND (
[Date] >= TODAY() - 14,
[Date] <= TODAY()
), 1, 0
)

IsInLast30Days =
IF (
AND (
[Date] >= TODAY() - 30,
[Date] <= TODAY()
), 1, 0
)

Create a table for silicer that will allow users to select the above range groups.

TableSlicer =
DATATABLE (
"Range", STRING,
{
{"Last 7 Days"},
{"Last 14 Days"},
{"Last 30 Days"},
{"Current Year"},
{"Custom"}
}
)

using selectedvalue function create one more measure to determine the selected date range.

with above tableslicer Eg.,  SelectedRange = SELECTEDVALUE(SlicerTable[Range], "Current Year")

create another final measure apply the date filter based on the user's selection.

DateFilter =
SWITCH (
[SelectedRange],
"Last 7 Days", [IsInLast7Days],
"Last 14 Days", [IsInLast14Days],
"Last 30 Days", [IsInLast30Days],
1 -- Default to include all dates for "Custom" selection

)

 

Try the above logic and let know.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thank you. When users select 'custom' from disconnected table, will that then default to show last 2 years in calendar....and then all visuals on the page will show 2 years worth of data (making it very messy)? as I'd like to show YTD for custom range to avoid the messy look...but still allow users to be able to select single dates or date ranges from the last 2 years.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors