Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Time intelligence issues with SAMEPERIODLASTYEAR and DATEADD functions

I create three calculated Tables from my Date Dimension Table 'Order Date' and I see different days than expected.

Table 1 = DATESYTD('Order Date'[Date])

Table 2 = DATEADD(DATESYTD('Order Date'[Date]),-1,YEAR)

Table 3 = SAMEPERIODLASTYEAR(DATESYTD('Order Date'[Date]))

 

Assume that today is the 9th of July 2019.

The first table shows as expected dates from the 1st of January of the current year till today (e.g., 01.01.2019-09.07.2019)

The second and third tables shows dates from the 1st of January of the previous year till end of the current month as of today (e.g., July) in last year (e.g., 01.01.2018-31.07.2018)!

 

I would expect Tables 2 and 3 to return the following dates 01.01.2018 - 09.07.2018 instead.

 

Do I miss something?

8 REPLIES 8
Anonymous
Not applicable

If "Order Dates' is A REAL DATE TABLE IN THE MODEL, then this is what you want:

 

[Dates YTD Up to Today] =
var __today = today()
RETURN
	CALCULATETABLE(
		DATESYTD( 'Order Dates'[Date] ),
		'Order Dates'[Date] <= __today
	)
	
[Dates YTD Up to Today Shifted 1 Year Back] =
var __today = today()
return
	CALCULATETABLE(
		SAMEPERIODLASTYEAR( DATESYTD( 'Order Dates'[Date] ) ),
		'Order Dates'[Date] <= __today		
	)

Best

Darek

Anonymous
Not applicable

The very fact that Table 1 ends on 9 July 2019 means that your Date dimension table 'Order Date' is not a real Date Table marked as such in the model. Sort out your model first. If the dimension is not correctly built, then the date intelligence functions will not work correctly.

 

Best

Darek

Anonymous
Not applicable

Dear Darek,

 

thank you for your reply. The Table 1 ends of 9th of July 2019 because of the DATESYTD function. So Actualy Table shows what I am expecting to see. I do not understand yous statement that "your Date dimension table 'Order Date' is not a real Date Table marked as such in the model".

 

The 'Order Date' table has a column [Date] of type Date with has dates from the begining of 2005 till the end of 2020. Could you elaborate further? Thanks.

Anonymous
Not applicable

If you had a real date table in your model, then applying DATESYTD to it would yield the full last year that exists in the table, in your case it would be the whole year 2020. I've checked it. This is because if there is no filter on the table, the last day visible would be the very last day and DATESYTD would then use this very last day and returned all the dates in the year up to this very last day.

 

Best

Darek

Anonymous
Not applicable

This is not my understanding of the DATESYTD function. The DATESYTD function should return all dates starting on the 1st of January of the current year till today irrespective of which is the last date in the Date Dimension.

Anonymous
Not applicable

Sorry but your understanding is wrong then. Please go to 

 

https://dax.guide/datesytd/

 

and read. You have to know that the function operates on filters. It has no knowledge about what the current day is today. If it did, it would be totally useless in calculations.

 

Best

Darek

Anonymous
Not applicable

I am still confused. What I read is "Returns a set of dates in the year up to current date" current date means that it knows what the curent day is.

 

If not then could you please give me a few examples of what DATESYTD would return given a specific inputs? Thank you for your time.

Anonymous
Not applicable

Current Date always means the current date as visible in the current filter context. As I said, if that was always the current real date, the function would be of no use.

 

If you select Sep-1999 in your date table, then DATESYTD( Dates[Date] ) will return dates from 1999-01-01 up to and including the last day of September 1999.

 

Best

Darek

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.