cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moezsajwani Regular Visitor
Regular 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
Super User

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!




Highlighted
HotChilli New Contributor
New Contributor

Re: Dateadd for incomplete months

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 52 members 1,377 guests
Please welcome our newest community members: