Forum Discussion

nestord's avatar
nestord
New Member
9 years ago
Solved

Specify custom date periods with predefined date periods.

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!

 

Need Help
Tips and Tricks
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

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

4 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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

    • nestord's avatar
      nestord
      New Member
      9 years ago

      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's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago
        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.

    • divya_1686's avatar
      divya_1686
      Frequent Visitor
      1 year ago

      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 ?