cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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?
Capture.JPG
 
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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Problems with Year to date comparisons

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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
Highlighted

Re: Problems with Year to date comparisons

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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Community Support
Community Support

Re: Problems with Year to date comparisons

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Problems with Year to date comparisons

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors