I have a sales table with dates and sale amount. Sale table is connected to Date Table which is used as a slicer in report. My report is based on months (coming from date table). I have calculated sales for last month using DATEADD -1, Month. However for current month i.e. October DATEADD returns me sale for complete September. What I want is same period last month so if my actual data in sales table is until 10th Oct, I want that function to return sales for 1 - 10th Sep instead of complete September. I can use daily interval in DATEADD but not all months have same days, so If I do -30, DAYS, the result for October 31 would be wrong.
Is there anyway around please? I am hoping this is a very common problem for many people.
The problem is not within the DATEADD because it gets the corressponding value from the month, your issue is with the date column itself.
When having time intelligence it will get the last day of the month as a maximum value so you are getting the full month if you change your slicer to one day earlier it will give only the 10 days of october.
To work around this you should add your date to the visual filter or report filter and the select the option Relative date filtering and select is in the last X days, months or years.
Should work as requested.