Reply
Regular Visitor
Posts: 16
Registered: ‎08-04-2017

Dateadd for incomplete months

Hi, 

 

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.

 

M

Super User
Posts: 2,397
Registered: ‎09-19-2016

Re: Dateadd for incomplete months

Hi @moezsajwani,

 

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.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Established Member
Posts: 159
Registered: ‎09-10-2018

Re: Dateadd for incomplete months

Can you use TOTALMTD   with something like DATEADD('your date field',-1,MONTH )?