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
Anonymous
Not applicable

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!

7 REPLIES 7
anamik_lahiri
New Member

Is this resolved? I am facing the same issue.

MarkCBB
Helper V
Helper V

Hi @Anonymous,

 

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. 

 

 

JdBernate
Frequent Visitor

@nikhilkabbin,

Use

 

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

DanYuncken
Helper I
Helper I

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

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!

v-jiascu-msft
Employee
Employee

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.
Anonymous
Not applicable

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.

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.