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

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 ?