cancel
Showing results for
Did you mean:
nikhilkabbin 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
v-jiascu-msft Super Contributor

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.
nikhilkabbin 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.

DanYuncken 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))))

DanYuncken 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!

JdBernate Frequent Visitor

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

Use

MarkCBB 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]))
PYTD UNITS = CALCULATE([YTD UNITS],DATEADD(LASTDATE('CALENDAR'[Date]),-1,YEAR))

Here are the MTD and PMTD

MTD UNITS = CALCULATE([TOTAL UNITS],DATESMTD('CALENDAR'[Date]))
PMTD UNITS = CALCULATE([MTD UNITS],DATEADD(LASTDATE('CALENDAR'[Date]),-1,MONTH))

The TOTAL UNITS measure is straightforward SUM

TOTAL UNITS = SUM('FACT'[UNITS])

I hope this helps you, let me know.

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 277 members 2,999 guests
Recent signins:
• arneh • duarte_afonso • dgharvey • DaveMaiden • dhannaa • Pulkit • smpa01 • hseedat • MicaelaTantum • pbi2020 • Avinash_Jha • lklingsheim • gollabach • maartenlauwaert 