Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
YB
Frequent Visitor

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,

 

 

 

1 ACCEPTED SOLUTION
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())))

 

View solution in original post

5 REPLIES 5
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
Community Champion
Community Champion

YTD Last Year =
CALCULATE(
    COUNT( 'TEST'[id] ),
    DATEADD( DATESYTD( 'Calendar'[Date] ), -1, YEAR )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

CNENFRNL
Community Champion
Community Champion

YTD Last Year =
CALCULATE(
    COUNT( 'TEST'[id] ),
    DATEADD(
        CALCULATETABLE( DATESYTD( 'Calendar'[Date] ), 'calendar'[Date] <= today ),
        -1,
        YEAR
    )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

YB
Frequent Visitor

Thanks, I gave it a try but it still didn't give me the desired result. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.