cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moezsajwani
Frequent Visitor

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

2 REPLIES 2
Super User III
Super User III

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


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Super User I
Super User I

Re: Dateadd for incomplete months

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors