Forum Discussion

BlueSky's avatar
BlueSky
Icon for Helper I rankHelper 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' 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 NumYearDateObservationsWTD
1201901/01/2019 1
1201902/01/2019 1
1201903/01/2019 1
1201904/01/2019 1
1201905/01/2019 1
1201906/01/2019                    2151
2201907/01/2019 1
2201908/01/2019 1
2201909/01/2019 1
2201910/01/2019 1
2201911/01/2019 1
2201912/01/2019 1
2201913/01/20192801
1202001/01/2020 1
1202002/01/2020 1
1202003/01/2020 1
1202004/01/2020 1
1202005/01/20201891
2202006/01/2020 1
2202007/01/2020 1
2202008/01/2020 1
2202009/01/2020 1
2202010/01/2020 1
2202011/01/2020 1
2202012/01/20202751
Need Help

3 Replies

  • 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

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    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/

    • BlueSky's avatar
      BlueSky
      Icon for Helper I rankHelper I
      6 years ago

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