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