Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AJthacker
New Member

Calculation for Last month Same Period

Hi All,

I am trying to calculate a measure for last month same period i.e. if today is 7.2.2019, I want to calculate sum(sales) for last month up to the 2nd day i.e. 6/2/2019. I have researched this and found a formula on most of the posts that I am trying to use but it doesn't give me the correct value.

Any help will be appreciated, thanks in advance!

Formula: Last MTD Sales=
CALCULATE(
Sum('Main'[Sales]),
DATEADD( DATESMTD( Dates[Date] ), -1, MONTH )

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Ensure that there is a relationship between the Date column of the Main Table to the Date column of the Calendar Table.  Drag the Date column from the Calednar Table to your slicer/visual and select today's date in the slicer/filter.  Write this measure

Last MTD Sales = CALCULATE(Sum('Main'[Sales]),DATESBETWEEN(Dates[Date],EOMONTH(MIN(Dates[Date]),-2)+1,EDATE(MIN(Dates[Date]),-1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Ensure that there is a relationship between the Date column of the Main Table to the Date column of the Calendar Table.  Drag the Date column from the Calednar Table to your slicer/visual and select today's date in the slicer/filter.  Write this measure

Last MTD Sales = CALCULATE(Sum('Main'[Sales]),DATESBETWEEN(Dates[Date],EOMONTH(MIN(Dates[Date]),-2)+1,EDATE(MIN(Dates[Date]),-1))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

Hi @AJthacker ,

 

The syntax of the measure seems ok but without any additional information is difficult to understand what you need and what is the final result you are looking for.

 

Can you share a sample data and expected result?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Try this

 

Formula: Last MTD Sales=
CALCULATE(
Totalmtd(dates[date],Sum('Main'[Sales]),
DATEADD( Dates[Date],-1,MONTH))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.