cancel
Showing results for
Did you mean:
Highlighted
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?

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
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.
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.
Highlighted
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

## Re: Problems with Year to date comparisons

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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!

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

Top Solution Authors
Top Kudoed Authors