cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avulasandeep
Helper III
Helper III

Difference of THIS YEAR YTD and LAST YEAR YTD

Hi  Everyone ,  

 

here i have one question that i need subtraction of YTD of one year with YTD of another Year. 

 

As i mentioned requirement in below screenshot.

req1.jpgreq 2.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please Tell me is that possible  the Difference of the YTD of  this  year - YTD of last year.

 

Thanks 

Sandeep

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Assuming you have a Calendar Table (if you don't, use the date field on current table), create YTD measure (change name of tables and columns):

 

VALUE YTD = 
IF(
	ISFILTERED('Calendar'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	TOTALYTD(SUM('TABLE'[VALUE]; 'Calendar'[Date].[Date])
)

Create Previous YTD measure:

 

VALUE PYTD = 
VAR pytddate1 = DATEADD(DATEADD(DATEADD(ENDOFYEAR ( 'Calendar'[Date] );-1;YEAR);1;DAY);-1;YEAR)
VAR pytddate2 = DATEADD ( ENDOFMONTH ( 'Calendar'[Date] ); -1; YEAR )
RETURN
CALCULATE (
    SUM['TABLE'[VALUE];
    FILTER (
        ALL ( 'Calendar'[Date] );
        'Calendar'[Date]
            >= pytddate1
            && 'Calendar'[Date] <= pytddate2
    )
)

Then calculate the difference:

 

VALUE PYTDG = [VALUE YTD] - [VALUE PYTD]

View solution in original post

4 REPLIES 4
v-huizhn-msft
Microsoft
Microsoft

Hi @avulasandeep,

You need a calendar table as @Anonymous said, and please mark the right reply as answer if you have resolved your issue.

Thanks,
Angelia

Anonymous
Not applicable

Assuming you have a Calendar Table (if you don't, use the date field on current table), create YTD measure (change name of tables and columns):

 

VALUE YTD = 
IF(
	ISFILTERED('Calendar'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	TOTALYTD(SUM('TABLE'[VALUE]; 'Calendar'[Date].[Date])
)

Create Previous YTD measure:

 

VALUE PYTD = 
VAR pytddate1 = DATEADD(DATEADD(DATEADD(ENDOFYEAR ( 'Calendar'[Date] );-1;YEAR);1;DAY);-1;YEAR)
VAR pytddate2 = DATEADD ( ENDOFMONTH ( 'Calendar'[Date] ); -1; YEAR )
RETURN
CALCULATE (
    SUM['TABLE'[VALUE];
    FILTER (
        ALL ( 'Calendar'[Date] );
        'Calendar'[Date]
            >= pytddate1
            && 'Calendar'[Date] <= pytddate2
    )
)

Then calculate the difference:

 

VALUE PYTDG = [VALUE YTD] - [VALUE PYTD]

View solution in original post

Greg_Deckler
Super User
Super User

So, if you have a separate Date table, then you should be able to use time intelligence functions and the general format of those would be:

 

Sales YTD = TOTALYTD(Sum('Sales'[Revenue]),'Date'[Date])

Sales LY YTD = CALCULATE ([Sales YTD], SAMEPERIODLASTYEAR ('Date'[Date]))

Sales Var = [Sales YTD]-[Sales LY YTD]

If you do not have a separate date table either create one or check out my Time Intelligence The Hard Way Quick Measure:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Anonymous
Not applicable

Well... I'm glad I also replied because I learned something... hehehe

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.