Last year week based on day name

Anonymous · ‎10-02-2019

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 · ‎10-02-2019

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 · ‎10-03-2019

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