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.
Hi,
I would like to be able to change the date range on a chart based on a slicer.
This would be similar to relative time slicer but I want to be able to define the time periods in the table and then use them in a slicer:
So if I select "Last 3 Days" my chart shows data for 3 days only, if I select "Last Month" my chart shows data for last month...
What is the best way to achieve that?
Solved! Go to Solution.
Hi @Anonymous ,
Create an extra table to list all avialble date periods for selection, which is unrelated to source data table.
Add below measure into visual level filter.
Measure = VAR selectedperiod = SELECTEDVALUE ( 'Date range'[Period] ) VAR last3days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 3, 1, 0 ) VAR last6days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 6, 1, 0 ) VAR lastweek = IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) && WEEKNUM ( MAX ( Test_1[Date] ), 2 ) = WEEKNUM ( TODAY (), 2 ) - 1, 1, 0 ) VAR last12days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 12, 1, 0 ) VAR last15days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 15, 1, 0 ) VAR lastmonth = IF ( MONTH ( TODAY () ) <> 1, IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) && MONTH ( MAX ( Test_1[Date] ) ) = MONTH ( TODAY () ) - 1, 1, 0 ), IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) - 1 && MONTH ( MAX ( Test_1[Date] ) ) = 12, 1, 0 ) ) RETURN IF ( selectedperiod = "Last 3 days", last3days, IF ( selectedperiod = "Last 6 days", last6days, IF ( selectedperiod = "Last Week", lastweek, IF ( selectedperiod = "Last 12 days", last12days, IF ( selectedperiod = "Last 15 days", last15days, IF ( selectedperiod = "Last Month", lastmonth, 0 ) ) ) ) ) )
Best regards,
Yuliana Gu
Hi @Anonymous ,
Create an extra table to list all avialble date periods for selection, which is unrelated to source data table.
Add below measure into visual level filter.
Measure = VAR selectedperiod = SELECTEDVALUE ( 'Date range'[Period] ) VAR last3days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 3, 1, 0 ) VAR last6days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 6, 1, 0 ) VAR lastweek = IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) && WEEKNUM ( MAX ( Test_1[Date] ), 2 ) = WEEKNUM ( TODAY (), 2 ) - 1, 1, 0 ) VAR last12days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 12, 1, 0 ) VAR last15days = IF ( MAX ( Test_1[Date] ) <= TODAY () && MAX ( Test_1[Date] ) > TODAY () - 15, 1, 0 ) VAR lastmonth = IF ( MONTH ( TODAY () ) <> 1, IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) && MONTH ( MAX ( Test_1[Date] ) ) = MONTH ( TODAY () ) - 1, 1, 0 ), IF ( YEAR ( MAX ( Test_1[Date] ) ) = YEAR ( TODAY () ) - 1 && MONTH ( MAX ( Test_1[Date] ) ) = 12, 1, 0 ) ) RETURN IF ( selectedperiod = "Last 3 days", last3days, IF ( selectedperiod = "Last 6 days", last6days, IF ( selectedperiod = "Last Week", lastweek, IF ( selectedperiod = "Last 12 days", last12days, IF ( selectedperiod = "Last 15 days", last15days, IF ( selectedperiod = "Last Month", lastmonth, 0 ) ) ) ) ) )
Best regards,
Yuliana Gu
Hi Yuliana,
Can you please explain "Add below measure into visual level filter.".
Can you please review with the current version of Power BI desktop.
Regards'
Parvez
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |