cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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 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]`
4 REPLIES 4
Microsoft Employee

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 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]`
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors