cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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
3 REPLIES 3
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
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

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]),

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors