Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a Fact transaction table with dates and would like to default the date slicer in my Power BI report to the latest date in the fact table and the 6 days previous, so effectively whenever anyone opens the report it always defaults to the latest available 7 days transactions. My fact table is usually 3 or 4 days behind today’s date so I cannot use the Relative slicer within the last 7 days as the filter. I have many visuals in my report and would obviously like them all to default to the last weeks activity.
I would also like the user to be able to select any earlier dates within the slicer after viewing the latest 7 days activity. Does anyone know how this can be achieved? I am able to get the dates I want into Measures by using LASTDATE and LASTDATE -6, but can’t seem to be able to incorporate these into the Date Slicer as the default values whenever my report opens.
Thank you very much.
Solved! Go to Solution.
If you want to use it in a slicer, you have to have the values in a Calculated Column, not in a Measure.
I would add a calculated column in your FactTable and calculate the number of days, then put these values into categories, "Last 7 days", "8 - 14 days", "15-21 days", etc. Then you can use this column as a slicer.
@SGP1 , You can try like
Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -6
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))
Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))
In case you need to show the trend
https://www.youtube.com/watch?v=duMSovyosXE
@SGP1 , You can try like
Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -6
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))
Period =
var _max =maxx(allselected(date),date[date])
var _min =_max -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]>=_max && date[date]<=_min))
In case you need to show the trend
https://www.youtube.com/watch?v=duMSovyosXE
If you want to use it in a slicer, you have to have the values in a Calculated Column, not in a Measure.
I would add a calculated column in your FactTable and calculate the number of days, then put these values into categories, "Last 7 days", "8 - 14 days", "15-21 days", etc. Then you can use this column as a slicer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |