Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I want to set default date for my from date and to date slicer. If I open the dashboard today it should show me default date of past month. Like if I open today (4 April) then the From date should be 1 March and To date should be 31 march in the slicer.
or if thats not possible then how to show sysdate - 30 dates in the slicer, like if I open today (4 april) then I should be able to see from date 4 march and to date 4 april.
I am using May 2023 Power BI Desktop version.
All suggestions are welcomed, thank you!
Solved! Go to Solution.
Hi @Jigyasa_11 ,
You might consider using "Preselected Slicer".
For the slicer that comes with Power BI, we cannot get it. But we can get the three point in the "Visualizations", and we click the "Get more visual":
Here are the steps you can follow:
1. we need to create a table with one column , and the value is True and False, like this:
2. Create a table – Slicer table.
Table 2 =
DISTINCT('Table'[Date])
3. we can create a measure to define which the date we need to default preselected. If we need to default preselect then we return True else return False.
Flag =
var _today=TODAY()
var _mindate=
EOMONTH(_today,-2)
var _maxdate=
EOMONTH(_today,-1)
return
IF(
MAX('Table 2'[Date])>_mindate&&MAX('Table 2'[Date])<=_maxdate,TRUE(),FALSE())
4. Create a measure to be placed in Visual's filter and set it to 1 to display the date according to our customized rules.
Flag_Test =
var _count=COUNTX(ALLSELECTED('Table 2'),[Date])
var _mindate=MINX(ALLSELECTED('Table 2'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table 2'),[Date])
return
SWITCH(
TRUE(),
_count=1&&MAX('Table'[Date])=_mindate,1,
_count>=2&&MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,1,0)
5. Result.
The default state shows the previous month's value
If you want to display individual values, you can just click on the individual dates:
Select two dates to show all dates between them.
Note: If you want to go back to the default state, click on the red circle in the upper right corner of the slicer above.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Jigyasa_11 ,
You might consider using "Preselected Slicer".
For the slicer that comes with Power BI, we cannot get it. But we can get the three point in the "Visualizations", and we click the "Get more visual":
Here are the steps you can follow:
1. we need to create a table with one column , and the value is True and False, like this:
2. Create a table – Slicer table.
Table 2 =
DISTINCT('Table'[Date])
3. we can create a measure to define which the date we need to default preselected. If we need to default preselect then we return True else return False.
Flag =
var _today=TODAY()
var _mindate=
EOMONTH(_today,-2)
var _maxdate=
EOMONTH(_today,-1)
return
IF(
MAX('Table 2'[Date])>_mindate&&MAX('Table 2'[Date])<=_maxdate,TRUE(),FALSE())
4. Create a measure to be placed in Visual's filter and set it to 1 to display the date according to our customized rules.
Flag_Test =
var _count=COUNTX(ALLSELECTED('Table 2'),[Date])
var _mindate=MINX(ALLSELECTED('Table 2'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table 2'),[Date])
return
SWITCH(
TRUE(),
_count=1&&MAX('Table'[Date])=_mindate,1,
_count>=2&&MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,1,0)
5. Result.
The default state shows the previous month's value
If you want to display individual values, you can just click on the individual dates:
Select two dates to show all dates between them.
Note: If you want to go back to the default state, click on the red circle in the upper right corner of the slicer above.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Jigyasa_11 ,
Thanks for the reply from @Idrissshatila , please allow me to provide another insight:
Here are the steps you can follow:
1. Create measure.
Flag =
var _today=TODAY()
return
IF(
MAX('Table'[Date]) >=DATE(YEAR(_today),MONTH(_today)-1,1)&& MAX('Table'[Date])<=EOMONTH(_today,-1),1,0)
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you for sharing this @v-yangliu-msft . Really helpful. So nice of you to share the file as well. Thanks a lot.
So what I am looking for is when user opens the dashboard then default date should be set to past 1 month dates. But after opening if the user wants then should be able to select any date to see data as per the choice of dates. Like when dashboard is opened date should be of March 1 to March 31, but then user should be able to select any date of past. Just at the time of first load of dashboard, default date should be of past 1month. With your solution I am able to default past 1 month but then all other past/future(Current month) dates are disabled.
Hello @Jigyasa_11 ,
check this out https://youtu.be/pt7dI8af_m8?si=6r2hPbWkTpFz0WkH
Proud to be a Super User! | |
Thank you for sharing this @Idrissshatila . But this doesnt solve my problem exactly.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |