Forum Discussion

AliH's avatar
AliH
Frequent Visitor
7 years ago

DAX YTD Help

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. ...
AliH's avatar
AliH
Frequent Visitor
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      AliH
      Frequent Visitor
      7 years ago

      Thanks, I'll dissect and have a go.

  • AliH's avatar
    AliH
    Frequent Visitor
    7 years ago

    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