cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsondervorst Frequent Visitor
Frequent Visitor

SAMEPERIODLASTYEAR with filter on month

Hello,

 

I'm having trouble with the SAMEPERIODLASTYEAR DAX formula.

Each month, we get new market sales figures ('monthly export') covering the past month and up to 25 months in the past. So in December 2016, we've received data from 11/2014 until 11/2016. The monthly data doesn't contain an exact day field, only the month (ex 201611).

So I created an extra column in my query, adding as day-value "01".

 

I'm calculating YTD sales and sales over the past month. Also, I'm calculating last year YTD sales over the same period and last year sales for the same month. For this, I'm using the SAMEPERIODLASTYEAR() function. However, I don't have a value for all of the competitiors/products in the same period last year, so this formula gives me an error.

  

Current formulas:

MTD € = CALCULATE(TOTALMTD(SUM('monthly export'[ValueMSP]);'monthly export'[Date]))
LY MTD € = CALCULATE([MTD €];SAMEPERIODLASTYEAR('monthly export'[Date]))
YTD € = CALCULATE(TOTALYTD(SUM('monthly export'[ValueMSP]);'monthly export'[Date]))
LY YTD € = CALCULATE([YTD €];SAMEPERIODLASTYEAR('monthly export'[Date]))

I also want to be able to use a filter/slicer on the month, so I can select a certain month, and it's automatically calculating the monthly sales of that month, the same month last year, the ytd sales until that month and the ytd sales until that month for last year. However, I have no idea on how to create this filter.

 

Would someone have an idea on how to get rid of the error and create this additional filter?

 

Thank you in advance!

 

11 REPLIES 11
kcantor Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

@jsondervorst

It appears that you are pulling your date from your fact table. In order for time intelligence to work, you need a dedicated date table with no missing days. You can use it for monthly, weekly, or quarterly data but it must contain contingious dates.

Check out this link for more information.

http://www.daxpatterns.com/time-patterns/



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
jsondervorst Frequent Visitor
Frequent Visitor

Re: SAMEPERIODLASTYEAR with filter on month

@kcantor

Thank you for your response.

So I linked the date field (01/11/2016) in my fact file to a date table created with following formula:

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date]; "2" );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

I then replaced in the formulas 'monthly export'[Date] with 'Date'[Date].

 

Now, I don't get the error anymore, but all my reports are empty? What am I missing?

Would you have a solution for the filter for the month?

 

Thank you in advance!

kcantor Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

@jsondervorst

Lets start with some smaller steps and questions. My understanding is that you want to look at ytd, mtd, yoy, and mom.

First, lets calculate a total. I genearlly build my calculations in steps to keep it simple when things break.

First, create a total sum: Total Sum = SUM('monthly export'[ValueMSP])

Last Month Sum is up next: Last Month = CALCULATE( [Total Sum], DATEADD(Date[Date], -1, month))

Then create last year sum: Total Sum Last Year= CALCULATE([Total Sum], DATEADD(Date[Date], -1, year))

 

Put your months on a table or chart, add the values. Then add a slicer showing month numbers.

Use measure in step sequence. You will notice that I only used SUM once. When I went back to calculate, I used the original measure name. This will help diagnose issues with measures and prevent having to write long calculations. As long as your month numbers are on the charts and table (row level on table) and your measures are in values, this should get you started.

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jsondervorst Frequent Visitor
Frequent Visitor

Re: SAMEPERIODLASTYEAR with filter on month

@kcantor

Okay, so I've created that measures.

"Put your months on a table or chart, add the values" -> when I take the 'month' field from the 'monthly export' table, the values for all three measures are the same (different per month though). When I tak the 'month' field from the 'date' table it gives me following error: "Calculation error in measure 'monthly export'[Last Month]: Function 'DATEADD' only works with contiguous date selections."

 

So it seems something is wrong with my date table?

kcantor Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

@jsondervorst

Contingious date errors mean that your date table is missing dates. It has to have all dates, even non working dates. It could also mean that you are selecting date sections that are skipping dates. For example, you cannot select January and July together without the months between.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




kcantor Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

@jsondervorst

Forgot to add that you need to use the month from the date table. It should tie all tables together. Always populate the axis and rows from your lookup/dimension tables and populate values from your fact tables.

Make sure you created a relationship between the tables as well.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-huizhn-msft Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

Hi @kcantor,

The dates returned from SAMEPERIODLASTYEAR function are the same as the dates returned by this equivalent formula:


DATEADD(dates, -1, year)

They all requires continuous dates as @jsondervorst posted.

 

Best Regards,
Angelia

kcantor Super Contributor
Super Contributor

Re: SAMEPERIODLASTYEAR with filter on month

Hi @v-huizhn-msft

I am aware that they require contingious dates. That was why I explained that the term meant no missing dates within your date table and that the error could appear if you were filtering months that were non-contingious. I am also aware that the functions are the same. I prefer DATEADD simply because it can be used for multiple years instead of just the same perior last year. Not sure why you felt I didn't know this.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jsondervorst Frequent Visitor
Frequent Visitor

Re: SAMEPERIODLASTYEAR with filter on month

@kcantor

 

How is it possible that my date table is not contiguous if I've used following formula to create it?

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date]; "2" );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

 No filter on the months...

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 458 members 3,791 guests
Please welcome our newest community members: