topic Re: Last year week based on day name in DAX Commands and Tips

Last year week based on day name

Anonymous — Thu, 03 Oct 2019 09:39:02 GMT

Hi all!

I need help with a dax function for last year data. I am using this function to calculate, for example, the revenue.

Revenue =

var calc =

CALCULATE(

sum(table[revenue]);

USERELATIONSHIP('TableDates [Date]'[date];table[date])

)

return

IF(calculo = BLANK() ; 0; calculo)

Revenue last year =

CALCULATE(

[Revenue];

DATEADD('TableDates [Date]'[date]; -1; YEAR)

)

When I am drilling down from month to week, the information of week last year is not the same (by month and days is working perfectly). That's because the seven days of the week 201829 are not the same in 201929LY. How can I modify the fuction to get the same number?

TableDates:

Error in the graph:

Re: Last year week based on day name

Anonymous — Wed, 02 Oct 2019 14:14:38 GMT

Hello Anonymous,

Have you tried using the DATESYTD function?

Instead of DATEADD('TableDates [Date]'[date]; -1; YEAR), you can use:

DATESYTD ( 'TableDates [Date]'[Date] )

More info: https://docs.microsoft.com/en-us/dax/datesytd-function-dax

Also, your table naming ('TableDates [Date]') is quite confusing as it has square brackets which are normally used for column notation.

Hope this helps!

Alex

Re: Last year week based on day name

Anonymous — Thu, 03 Oct 2019 09:37:35 GMT

Thanks for your answer Alex,

In this case we have several dates table so we have this names in brackes to know which one we have to use, just a name.

I have seen that DATESYTD https://dax.guide/datesytd/ function will sum all the values in the year, but I need to compare year and year-1 in two lines., gettin the same value in 201829 tan 201929 - Previous year. I have uploaded the question with an example.

Anonymous wrote:
Hello Anonymous,

Have you tried using the DATESYTD function?

Instead of DATEADD('TableDates [Date]'[date]; -1; YEAR), you can use:
DATESYTD ( 'TableDates [Date]'[Date] )
More info: https://docs.microsoft.com/en-us/dax/datesytd-function-dax

Also, your table naming ('TableDates [Date]') is quite confusing as it has square brackets which are normally used for column notation.

Hope this helps!
Alex

Re: Last year week based on day name

TomMartens — Thu, 03 Oct 2019 10:20:41 GMT

Hey,

this article

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

will provide additional hints how to find the "exact" same timeframe in the past, of course one has to decide how leap years have to be handles.

Regards,

Tom