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 All,
I need to have posibility in the report to specify either predefined period(like last 7 days, last 30 days...) or custom date period(any possible date renge). And i have to compare measures for current and previous period. In order to compare two periods i added few tables(2 for current periods and two for previous ).
For custom period selection just need to use slicer on date field and it works. Periods i specified in DatePeriod and DatePeriod2 respectivly. But im not able to get it work together. Report should filter data based on custom date(date slicer), only in "Custom Date" is selected in DatePeriod filter. And vice verce if i change DatePeriod slicer it should ignor/or reset what is specified in Date slicer.
Is the any way to make it work together?
Thanks in advance for any help!
Solved! Go to Solution.
I'll use two measures(starting date and end date) and two tables(one lookup table and one calendar table). Then those two measures can be used to filter tables. Check more details in the attached pbix.
starting date = SWITCH(MAX('peroid lookup'[starting day]),DATE(1970,1,1),MIN('calendar'[Date]),MAX('peroid lookup'[starting day])) end date = SWITCH(MAX('peroid lookup'[starting day]),DATE(1970,1,1),MAX('calendar'[Date]),TODAY()) ####The calculated column in lookup table starting day = SWITCH ( TRUE (), 'Peroid lookup'[Peroid] = "Last 07 days", TODAY () - 7, 'Peroid lookup'[Peroid] = "Last 14 days", TODAY () - 14, 'Peroid lookup'[Peroid] = "Last 30 days", TODAY () - 30, 'Peroid lookup'[Peroid] = "Last 90 days", TODAY () - 90, 'Peroid lookup'[Peroid] = "Today", TODAY (), DATE ( 1970, 1, 1 ) )
I'll use two measures(starting date and end date) and two tables(one lookup table and one calendar table). Then those two measures can be used to filter tables. Check more details in the attached pbix.
starting date = SWITCH(MAX('peroid lookup'[starting day]),DATE(1970,1,1),MIN('calendar'[Date]),MAX('peroid lookup'[starting day])) end date = SWITCH(MAX('peroid lookup'[starting day]),DATE(1970,1,1),MAX('calendar'[Date]),TODAY()) ####The calculated column in lookup table starting day = SWITCH ( TRUE (), 'Peroid lookup'[Peroid] = "Last 07 days", TODAY () - 7, 'Peroid lookup'[Peroid] = "Last 14 days", TODAY () - 14, 'Peroid lookup'[Peroid] = "Last 30 days", TODAY () - 30, 'Peroid lookup'[Peroid] = "Last 90 days", TODAY () - 90, 'Peroid lookup'[Peroid] = "Today", TODAY (), DATE ( 1970, 1, 1 ) )
@Eric_Zhang Thank you for your response. How you would suggest to calculate measure for those periods? Calculate masure for date range "starting date"\"end date"?
@nestord wrote:
@Eric_Zhang Thank you for your response. How you would suggest to calculate measure for those periods? Calculate masure for date range "starting date"\"end date"?
Yes, in the other measures' DAX fomular, filtter tables with those two measures.
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 |