Setting the Default Value of a Slicer to current fortnight

[JohnA]

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

[v-qiuyu-msft · Accepted solution]

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