Specify custom date periods with predefined date periods.

[nestord]

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!

[Eric_Zhang · Accepted solution]

@nestord

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 )
)

[divya_1686]

This method works perfectly.The only glitch being when selecting something in the custom date range(second slicer) and then toggling the first time period selector.The selected date range filter gets stuck and doesnt blank out .Have you encountered a similar behaviour ?

[nestord]

@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"?

[Eric_Zhang]

---

@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.