Last year week based on day name

[Anonymous]

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:

[Anonymous]

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

[Anonymous]

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

---

[TomMartens]

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