Forum Discussion
Advocate IISetting default dates in slicer for financial report
Hi,
I have a financial report where there is a date slicer with a start and end date. I would like to set the default start date to the start of the current fiscal year and the end date to today's date.
Would you be able to advise the best way of doing this ? The model is a live connection so would like to create report level functionality.
thanks
Scott
Hi PaisleyPrince,
Thank you for the update. The behavior you're experiencing is expected, as the FY-YTD measure is set as a page or report filter, which limits the slicer to the current fiscal year. If you'd like the slicer to default to the current fiscal year but still allow users to select dates as far back as 1/4/22, you'll need to remove that filter and use a bookmark instead. Set the slicer to cover 1 April (the start of the current FY) to today, save this as a bookmark, and set it as the default view for the report. This will ensure the report opens with the current fiscal range, while still giving users the option to adjust the slicer to include earlier years. Let me know if you need instructions for creating the bookmark.
Thank you.
9 Replies
Hi PaisleyPrince ,
1. If you can change the model. Ask whoever owns the semantic model (SSAS / Power BI dataset) to add logic on the Date table. Then you use a normal date slicer plus a default filter that auto-applies current FY-to-date.
Follow below steps.
Step 1 . Fiscal-YTD flag (in the model)
On the Date table, create a measure.
IsInCurrentFiscalYTD :=
VAR TodayDate = TODAY()
VAR FiscalYearStart =
DATE(
YEAR ( TodayDate ) - IF ( MONTH ( TodayDate ) < 4, 1, 0 ),
4,
1
)
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
IF (
CurrentDate >= FiscalYearStart &&
CurrentDate <= TodayDate,
1,
0
)
This assumes fiscal year runs 1 April – 31 March.
Step 2. Use this as a default filter
In your report:
Put your normal Between Date slicer (using 'Date'[Date]).
On the page filters (or report filters), add IsInCurrentFiscalYTD.
Set the filter to IsInCurrentFiscalYTD = 1.
Now:
When the report opens, everything is already filtered to “current FY start → today”.
The slicer will show that range by default because it respects the filter.
As time moves on, the range automatically rolls forward with TODAY().
If my response as resolved your issue please mark it as solution and give kudos.
- amitchandak
Super User
PaisleyPrince , I doubt, as of now, you can default between the date slicer. We can use the relative date slicer if that is suitable.
If you use two slicers, then we can have fixed text to save it
example
Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1*month(Today())),"Last year Last Month" ,
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)
Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")
We can select this month, last year same month, FY start etc and save it and same way in another slicer - Praful_Potphode
Hi PaisleyPrince ,
Please try the option suggested by amitchandak
if it doesn't work please share more information on input/output.
Thanks and Regards,
Praful
PaisleyPrinceHi,
I'm not sure that it gives me what i'm looking for. Just to recap on the ask, i'm looking to have a standard date slicer set to 'between' and the start date defaulting to 1 April (start of the current financial year) and the end date defaulting to today's date.
thanks
Scott
v-sgandrathiCommunity Support
Hi PaisleyPrince,
Thank you Anonymous Praful_Potphode amitchandak for your response to the query,
Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.- PaisleyPrince
Hi,
thanks for the assistance. The measure works on the date slicer in order to default to the start of the current financial year, however i have also a need for users to select data as far back as 1/4/22. With this solution it appears that this is not possible. Can you please advise ?
thanks
Scott
- v-sgandrathi
Hi PaisleyPrince,
Just looping back one last time to check if everything's good on your end. Let me know if you need any final support happy to assist if anything’s still open.
Thank you. - v-sgandrathi
Hi PaisleyPrince,
Thank you for the update. The behavior you're experiencing is expected, as the FY-YTD measure is set as a page or report filter, which limits the slicer to the current fiscal year. If you'd like the slicer to default to the current fiscal year but still allow users to select dates as far back as 1/4/22, you'll need to remove that filter and use a bookmark instead. Set the slicer to cover 1 April (the start of the current FY) to today, save this as a bookmark, and set it as the default view for the report. This will ensure the report opens with the current fiscal range, while still giving users the option to adjust the slicer to include earlier years. Let me know if you need instructions for creating the bookmark.
Thank you.
- v-sgandrathi
Hi PaisleyPrince,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.
Thank you for your cooperation. Have a great day.
