cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnA Frequent Visitor
Frequent Visitor

Setting the Default Value of a Slicer to current fortnight

Hi all,

 

I'm quite new to this so apologies for any dumb questions.  I have several reports that have a date slicer to produce the results for the selected dates.  Some of these reports are based on employee pay cycles which are fortnightly.  I would like the report to default to the last complete pay cycle so the user is looking at the most current complete data when they run the report.  For example, if the pay cycles were as follows:

 

26/06/2017 - 09/07/2017

10/07/2017 - 23/07/2017

24/07/2017 - 06/08/2017

07/08/2017 - 20/08/2017

 

So from these examples, if the report was run today (17/08/2017) it would default to the date range 24/07/2017 - 06/08/2017 as that was the last complete cycle.  I also require the user to be able to use the full functionality of the slicer and select different time frames as required, which wouldn't necessarily be fortnightly.  Hope this all makes sense.

 

Many thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-qiuyu-msft
Moderator

Re: Setting the Default Value of a Slicer to current fortnight

Hi @JohnA,

 

Assume the Range column contains the date range (26/06/2017 - 09/07/2017,etc,...) you provided, you can create a calculated column to decide whether the date range is the most recent complete:

 

Column =
var LatestCD=CALCULATE(MAXX('Table1',DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2))),FILTER(ALL(Table1),DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2))<=TODAY()))
return
IF(LatestCD=DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2)),1,0)

 

Then drag this calculated column to the Page Level filter, and choose the value "1", it will default to display data in most recent range.

 

Best Regards,
QiuyunYu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Moderator v-qiuyu-msft
Moderator

Re: Setting the Default Value of a Slicer to current fortnight

Hi @JohnA,

 

Assume the Range column contains the date range (26/06/2017 - 09/07/2017,etc,...) you provided, you can create a calculated column to decide whether the date range is the most recent complete:

 

Column =
var LatestCD=CALCULATE(MAXX('Table1',DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2))),FILTER(ALL(Table1),DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2))<=TODAY()))
return
IF(LatestCD=DATEVALUE(PATHITEM(SUBSTITUTE('Table1'[Range], "-", "|"), 2)),1,0)

 

Then drag this calculated column to the Page Level filter, and choose the value "1", it will default to display data in most recent range.

 

Best Regards,
QiuyunYu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.