Forum Discussion

YB's avatar
YB
Frequent Visitor
4 years ago
Solved

Need help with TOTALYTD

Hi folks,

 

I have been trying to figure out how to compare measures year over year for the same time period.  In this particular case I'd like to get from Jan 1st to Today (including the same time period for last year and the previous year).  I recently discovered that TOTALYTD seems to automatically include the end of the current month.  So I noticed the count for this year looked fine because there is no current data past today.  The problem comes when comparing data to the previous year (or year before).

 

The following measures (and variants) work just fine for the current year. 

 

Year to Date = TOTALYTD(COUNT('TEST'[id]),'Calendar'[Date])
Year to Date = TOTALYTD(COUNT('RTEST'[id]),'Calendar'[Date], 'Calendar'[Date] <= TODAY())

Year to Date = CALCULATE(COUNT('TEST'[id]), DATESYTD('Calendar'[Date]))
Year to Date = CALCULATE(COUNT('TEST'[id]), DATESYTD('Calendar'[Date]), 'Calendar'[Date] <= TODAY())

 

 

However, I'm having a difficult time applying the same logic for previous years.

 

YTD Last Year = TOTALYTD(COUNT('TEST'[id]),DATEADD('Calendar'[Date], -1, year))
YTD 2 Years Ago = TOTALYTD(COUNT('TEST'[id]),DATEADD('Calendar'[Date], -2, year))

 

The above will get everything from Jan 1st 2020 through Oct 31st 2020 when I just need to go to TODAY -1 year.

 

Logically I feel like I need to use the filter but it doesn't support the TODAY() function with the DATEADD() function.

 

Any help would be appreciated!

 

Thanks,

 

 

 

  • Ok, I found a solution but it's not very elegant.

     

     

    YTD Last Year = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -1, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
    
    YTD 2 Years Ago = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -2, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

     

5 Replies

  • YB's avatar
    YB
    Frequent Visitor

    Ok, I found a solution but it's not very elegant.

     

     

    YTD Last Year = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -1, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
    
    YTD 2 Years Ago = CALCULATE([Year to Date], DATEADD ('Calendar'[Date], -2, YEAR ), 'Calendar'[Date] <= DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

     

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      YTD Last Year =
      CALCULATE(
          COUNT( 'TEST'[id] ),
          DATEADD( DATESYTD( 'Calendar'[Date] ), -1, YEAR )
      )
      • YB's avatar
        YB
        Frequent Visitor

        Unfortunately, this does not work and it is very similar to what I was already attempting before.  This will include all dates in October of last year not just up to the 5th (today).