Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Values | Date | Reporting Week | Hi Guys, What I need is for my dashboard tab to open with the last two reporting weeks as default, so data showing for weeks "26/03/2018 & 02/04/2018", not last two current weeks from todays date. Thanks guys | |
100 | 01/01/2018 | 01/01/2018 | ||
200 | 01/01/2018 | 08/01/2018 | ||
300 | 01/01/2018 | 15/01/2018 | ||
400 | 01/01/2018 | 22/01/2018 | ||
500 | 01/01/2018 | 29/01/2018 | ||
600 | 01/02/2018 | 05/02/2018 | ||
700 | 01/02/2018 | 12/02/2018 | ||
800 | 01/02/2018 | 19/02/2018 | ||
900 | 01/02/2018 | 26/02/2018 | ||
1000 | 01/03/2018 | 05/03/2018 | ||
1100 | 01/03/2018 | 12/03/2018 | ||
1200 | 01/03/2018 | 19/03/2018 | ||
1300 | 01/03/2018 | 26/03/2018 | ||
1400 | 01/04/2018 | 02/04/2018 |
Solved! Go to Solution.
Hi @KH11NDR,
Based on my test, you could refer to below steps:
Add index column in query editor.
Create a calculated column
Type = IF([Index]=MAX('Table1'[Index]),"Last 2",IF([Index]=MAX([Index])-1,"Last 2","Not Last two"))
Filter the "Last 2" in Report level filters and now you could get the result.
Regards
Daniel He
Thanks guys, I'm sure both worked, but I used the bottom one, tweaked it a bit and it works like a treat.
Hi @KH11NDR,
Based on my test, you could refer to below steps:
Add index column in query editor.
Create a calculated column
Type = IF([Index]=MAX('Table1'[Index]),"Last 2",IF([Index]=MAX([Index])-1,"Last 2","Not Last two"))
Filter the "Last 2" in Report level filters and now you could get the result.
Regards
Daniel He
Any takers?
Hi
You can create a column using Power Query by sorting the date column descending and add an index column and then add a conditional column
= Table.AddColumn(#"Added Index", "Custom", each if [Index] <= 14 then "Last 2 weeks" else "Not last 2 weeks")
or you could create a Calculated Column
DAX Column = IF([Date]<=CALCULATE(MAX(Reporting[Date]);ALL(Reporting))-14;"Not Last 2";"Last 2")
And then insert the column as a slicer and set the value to last 2 and publish the report
/Erik
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |