cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal
Post Prodigy
Post Prodigy

Total month to date for last year until a specific day of the month

I want to calculate the sum of MTD sales per day for the same period of last year, only until a specific day.

So for example, if the filter is year= 2019 and month= 2, I need the MTD sales for february 2018 until the 5th, calculated day by day:

MTDSales=
VAR MyDay = 5
RETURN
CALCULATE(
  TOTALMTD(SUM(Sales); Calendar[Date]);
  SAMEPERIODLASTYEAR(Calendar[Date]);
  //here I need another filter to stop on the 5th!
)
1 ACCEPTED SOLUTION
TomMartens
Super User II
Super User II

Hey,

 

please have a look here:

https://www.daxpatterns.com/time-patterns/

 

I think that almost every time intelligence calculation is covered here.

 

Please be alos aware that sooner or later you have to decide how to treat the upcoming leapyear in 2020, but there are some considerations about the same timeframe if a leapyear has to be considered in one way or the other.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User II
Super User II

Hey,

 

please have a look here:

https://www.daxpatterns.com/time-patterns/

 

I think that almost every time intelligence calculation is covered here.

 

Please be alos aware that sooner or later you have to decide how to treat the upcoming leapyear in 2020, but there are some considerations about the same timeframe if a leapyear has to be considered in one way or the other.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Sure, I've read all that before, but no pattern like this one.

Hey,

 

please provide a pbix that contains sampole data and represents your data model.

Upload the file to onedrive and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors