DAX YTD Help

[AliH]

Hi All,

I'm struggling to figure out how to do a bespoke YTD calculation, essentially what we are trying to achieve is that for any given filter year, display the equivalent YTD for that year.

For examples:

	2015	2016	2017	2018	2019
	Turnover £	Turnover YTD £	Turnover £	Turnover YTD £	Turnover £	Turnover YTD £	Turnover £	Turnover YTD £	Turnover £	Turnover YTD £
Product 1	100	50	200	100	150	75	300	150	350	350
Product 2	40	20	50	25	100	50	180	90	200	200
Product 3	200	200	300	225	400	280	800	750	1000	1000

So for the current year the Turnover £ and YTD £ will be the same, but for previous years it will be the equivalent time period for the filter year.

Here are the current measures:
Turnover £:=Orders[Order Value]-Credits[Credit Value]

I think I need my YTD measure to be something like CALCULATE(Orders[Turnover £], Dates[DayofYear] <= lastdate(Orders[Invoice Date])) But obviously this isn't how it is done.

Does anyone have any pointers please?

Thanks you in advance,

Alister

[TomMartens]

Hey,

I recommend to start with this article, almost everything timerelated is covered here: https://www.daxpatterns.com/time-patterns/

Regards,Tom

[AliH]

Hi Tom,

Thanks for the response. I've read through that before and it is a great article. What I found there only worked for the current year, but when another year was the filter, the measure displayed all of that year, instead of the equivalent YTD - I maybe should have changed the title of this post.

I'll have another read and see what I can figure out. I was hoping that this was a common one with an obvious answer I am missing.

Many thanks,

Alister

[Anonymous]

Hi there.

Mate, if you say YTD, it's obvious that the total value of any measure for the whole year will be equal to the YTD (of the measure in question) for the same year. This follows from the very definition of YTD. So, either you want to calculate something different than YTD or... you should give us a clear example of what it is you want.

Otherwise, we'll be chasing our tail, so to speak.

Best

Darek

[AliH]

Hi Darek,

I probably could have explained it better, but I don't know the term _equivalent YTD period_. So the equivalent time period that would be YTD this year, 1st Jan to 3rd July (at time of posting), for ny selected year.

The following is almost there:

CALCULATE ( [Turnover £], DATESBETWEEN ( Dates[Date], FIRSTDATE(Dates[Date]), DATE(YeaR(max(Dates[Date])),month(today()),day(today())) ) )

I'm now working on trying to amend the dateto in the datesbetween function to be using a constant date based on the lastdate of another datefield. CALCULATE(LASTDATE(Orders[Invoice Date]), ALL....)) but it isn't working quite right yet.

Many thanks,

Alister

[Anonymous]

I guess you want something like this:

[YTD with TODAY shifted as upper limit] :=
var __today = TODAY()
var __todaysYear = YEAR( __today )
var __visibleYear = SELECTEDVALUE( 'Dates'[Year] )
var __oneYearVisible = HASONEVALUE( 'Dates'[Year] )
var __numberOfYearsBackFromToday = __todaysYear - __visibleYear
var __upperDayLimit = EDATE( __today, -12 * __numberOfYearsBackFromToday ) 
var __ytd =
    CALCULATE(
        [My Measure],
        DATESYTD( 'Dates'[Date] ),
        'Dates'[Date] <= __upperDayLimit
    )
return
    if( __oneYearVisible, __ytd )

Best

Darek

[AliH]

Thanks, I'll dissect and have a go.