cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

@MFelix,  @Greg_Deckler, any ideas?

Thanks very much

Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

I would think that @marcorusso might like to dig into this one. All I can say is this is the kind of reason that I creaed the Time Intelligence The Hard Way Quick Measure:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

The time intelligence functions are just these mysterious little black boxes that just act downright weird at times. I frankly don't really trust them as far as I can try catch throw them.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

Thanks @Greg_Deckler

Yeah, I don't like them much either. They kinda shady. Let's see if @marcorusso can enlighten us then, as usual.

I would give you kudos but it's not working Smiley Frustrated, just like a bunch of other features lately on this site.   

Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

@AlB - Clear your browser cache, that worked for me to clear up all the issues.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

@AlB - Try clearing your browser cache, that worked for me to clear up all the issues.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

@Greg_Deckler

I tried that, even the clering the  whole history, but nothing changes. I'm told they are trying to fix it. 

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

This is just another case where the Time Intelligence functions don't work as expected because the Date table is wrong.

Remember: Time Intelligence functions are guaranteed to work *only* if the Date table is "complete" - meaning you need all the dates until the end of the year.

Not doing that, what happens is that the year 2019 seems made by 15 days, so if you include all of them than it seems you have an entire month, or an entire year.... You are violating the assumption made by Time Intelligence and you cannot trust any result at that point.

 

View solution in original post

Community Support Team
Community Support Team

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

hi, @AlB

Unfortunately, So far I haven't got any reply about this question.

and I read these in the book The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Service... by Marco Russo and Alberto Ferrari.

in page 173:

8.JPG

 The following two definitions of PY YTD Sales are equivalent, even if the second one could be slightly faster (but barely measureable).

 

 

and in your case, date table is from "2018,1,1" to today, you may try this formula

Date Table = ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(YEAR(TODAY()),12,31)),
"Year", YEAR ( [Date]),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Monthnumber", FORMAT ( [Date], "MM" )
    )

 add a judge column for date table

judge = IF('Date Table'[Date]<=TODAY(),1,2)

Then add it to report level filter and set it "1"

 

Result:

9.JPG

 

This is just as a reference, I also think reason for this case is that date table is incomplete.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

Highlighted
chrischiappazzo Regular Visitor
Regular Visitor

Re: SAMEPERIODLASTYEAR(DATESYTD( )) different from DATESYTD(SAMEPERIODLASTYEAR( ))??

@AlB @v-lili6-msft @marcorusso @Greg_Deckler @MFelix 

 

I am encountering a similiar problem with my data set at the moment, and both of the formulas (attached) here are resulting a value that is until end of month, rather than (ie today up to 08/05/19). I am not sure if the reason for this is that i am using a relationship to connect my data table to a calendar table, as my data is not contiguous, so results in an error when a date slicer is used...

 

I am using

LY_YTD Event Type = CALCULATE(COUNT([Event Type]),DATESBETWEEN('Calendar'[Date],FIRSTDATE(DATEADD('Calendar'[Date],-365,DAY)),LASTDATE(DATEADD('Calendar'[Date],-365,DAY))))

 

and i am getting the correct result, however it is quite ugly and seems to have some bugs,

ie. if i change the date range to all of 2018, it outputs a value that is the sum of 2018 and 2019 to date..

Do you know of any other formulas that work for calculating sum of same period last year to the current date?

I want to be able to create some extra graphs from this and a better formula would be helpful

 

YTDPreviousYearRevenue =
CALCULATE (
    SUM ( Revenue[Revenue] );
    SAMEPERIODLASTYEAR ( DATESYTD ( 'Date Table'[Date] ) )
)
YTDPreviousYearRevenue_2 =
CALCULATE (
    SUM ( Revenue[Revenue] );
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date Table'[Date] ) )
)

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)