Forum Discussion

BlueSky's avatar
BlueSky
Helper I
6 years ago
Solved

Problems with Year to date comparisons

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' colou...
Need Help
  • MattAllington's avatar
    MattAllington
    6 years ago

    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/

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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