cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nikhilkabbin Regular Visitor
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
Community Support Team

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

Hi @nikhilkabbin,

 

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

 

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

Then create the measure like below.

a

 

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
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
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
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
Frequent Visitor

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

@nikhilkabbin,

Use

 

Calculate(salesMTD,dateadd(dates,-1,year))

MarkCBB Member
Member

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

Hi @nikhilkabbin,

 

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.