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
jsondervorst
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
Community Champion
Community Champion

@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 Super User!




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

@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 Super User!




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

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 Super User!




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

@jsondervorst

Do you have any other filters on the page? If you are looking at monthly data, for example and select May, if you don't specify which year and your data has multiple years, it will give you that same error. Try placing a year filter on the page, select one year and then try to use a month filter.





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

Proud to be a Super User!




@jsondervorst

 

can you share the relationship between your tables?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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?

@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 Super User!




kcantor
Community Champion
Community Champion

@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 Super User!




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.