cancel
Showing results for
Did you mean:
Regular Visitor

## DAX Previous Year Month to date total is giving entire previous month's entire total

Hi All,

When I try to use SAMEPERIODLASTYEAR to get MTD of previous year, it returns entire months sales sum, rather I was looking for only till current year date sum.

For example:

Current data is till 10th April I need to calculate sum of sales

MTD of current year (10th April 2018) and MTD (Till 10th April 2017)

Thanks!

6 REPLIES 6
Community Support Team

## Re: DAX Previous Year Month to date total is giving entire previous month's entire total

You could create a date table with the formula below first.

Table = CALENDAR(DATE(2017,1,1),TODAY())

Then create the measure like below.

Measure = CALCULATE(SUM(Amount[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

'Table '[Date] is a column from another table I created and create the relationship between the two tables.

The result output is below.

You could have a reference of this pbix file.

If you need additional help please share some data sample and expected output.

Best regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

## Re: DAX Previous Year Month to date total is giving entire previous month's entire total

Thanks @v-jiascu-msft I used "SAMEPERIODLASTYEAR" it is working for monthly data but not for daily data. What I mean is "SAMEPERIODLASTYEAR" is calculating for entire month previous period not only till date current year.

I will share Sample report if this is not clear.

Frequent Visitor

## Re: DAX Previous Year Month to date total is giving entire previous month's entire total

Hi nikhilkabbin,

Did you find a solution for this? I’m having the same problem. I also noticed this occurs for the DATEADD function too.

To test, I created a data model in PowerPivot containing only a date table starting January 1, 2016 and ending May 4, 2018.

The formula: Last Date In Table:=LASTDATE('Date Table'[Date]) returned the May 4, 2018, as expected

This month to date type formula below also returned the May 4, 2018:

Last Date in selected period:=CALCULATE(LASTDATE('Date Table'[Date]),DATESBETWEEN('Date Table'[Date],DATE(2018,5,1),DATE(2018,5,4))

Why does adding SAMEPERIODLASTYEAR return the May 31 2017 instead of May 4, 2017 in the formula below?

Last Date in selected period year ago:=CALCULATE(LASTDATE('Date Table'[Date]),SAMEPERIODLASTYEAR(DATESBETWEEN('Date Table'[Date],DATE(2018,5,1),DATE(2018,5,4))))

Frequent Visitor

## Re: DAX Previous Year Month to date total is giving entire previous month's entire total

I am hoping to avoid writing formulas like this

Month Sales To Date:=

Var

End_Date_This_Year = LASTDATE('Date Table'[Date])

Return

Var

End_Date_Last_Year = DATE(

YEAR(End_Date_This_Year)-1,

MONTH(End_Date_This_Year),

DAY(End_Date_This_Year)

)

Return

CALCULATE(

SUM('Sales Table'[Sales]),

DATESBETWEEN(

Date Table'[Date],

STARTOFMONTH(End_Date_Last_Year)

End_Date_Last_Year

)

)

Thanks!

Frequent Visitor

Use

Member

## Re: DAX Previous Year Month to date total is giving entire previous month's entire total

I had a similar problem, with YTD as well as MTD, the measures for the previous were calculating on the full month and not until the current date.

The first thing that I did is limit my date table to always be up until the latest date in my fact table. Then the below measures will work.

Here are the YTD and PYTD

```YTD UNITS = CALCULATE([TOTAL UNITS],DATESYTD('CALENDAR'[Date]))

Here are the MTD and PMTD

```MTD UNITS = CALCULATE([TOTAL UNITS],DATESMTD('CALENDAR'[Date]))
`TOTAL UNITS = SUM('FACT'[UNITS])`