The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
102 | |
82 | |
62 | |
55 |
User | Count |
---|---|
253 | |
119 | |
115 | |
95 | |
70 |