Problems with Year to date comparisons

[BlueSky]

Hi,

I'm trying to do a simple year to date vs last year to date, however running into some difficulties.

My table shows year and week number.

For 2020 week 1, I expect the 'Last Year' coloum to show 215, it's blank.  Any ideas?

This Year = CALCULATE(

sum(MockUp[Observations])

,MockUp[WTD]=1

)

Last Year = CALCULATE([mSumDeathsYTDv2], DATEADD(MockUp[Date],-1,YEAR), ALL(MockUp[Year], MockUp[WTD], MockUp[Date] ))

Week Num	Year	Date	Observations	WTD
1	2019	01/01/2019		1
1	2019	02/01/2019		1
1	2019	03/01/2019		1
1	2019	04/01/2019		1
1	2019	05/01/2019		1
1	2019	06/01/2019	215	1
2	2019	07/01/2019		1
2	2019	08/01/2019		1
2	2019	09/01/2019		1
2	2019	10/01/2019		1
2	2019	11/01/2019		1
2	2019	12/01/2019		1
2	2019	13/01/2019	280	1
1	2020	01/01/2020		1
1	2020	02/01/2020		1
1	2020	03/01/2020		1
1	2020	04/01/2020		1
1	2020	05/01/2020	189	1
2	2020	06/01/2020		1
2	2020	07/01/2020		1
2	2020	08/01/2020		1
2	2020	09/01/2020		1
2	2020	10/01/2020		1
2	2020	11/01/2020		1
2	2020	12/01/2020	275	1

[v-shex-msft]

Hi @BlueSky,

As MattAllington said, time intelligence functions not suitable for your scenario. I'd like to suggest you extract current 'year' and 'week num' as condition parameter to filter records:

Last Year =
VAR currYear =
    MAX ( MockUp[Year] )
RETURN
    CALCULATE (
        MAX ( MockUp[Observations] ),
        FILTER ( ALLSELECTED ( MockUp ), [Year] = currYear - 1 ),
        VALUES ( MockUp[Week Num] )
    )

Regards,

Xiaoxin Sheng

[MattAllington · Accepted solution]

The inbuilt time intelligence functions that you are using work on dates, not week numbers. So 3 Jan May be week 2 one year and week 1 the year after (for example). If you want to report on like for like week numbers, you need to write a custom time intelligence function.  Read my article here 

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

[BlueSky]

Thanks Matt, very well written blog post, helped a lot.