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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
drrai66
Resolver I
Resolver I

Find YTD This, Last Year and Percent YTD Change

Hello Experts,

So I searched a lot, but got  bit confused how to apply to my data which Looks Like This. It is Sample data for 2 Year Incidents, My actual dataset is bigger. So I have Application ID, Date of Incident and Number of Incidents in following data set.

Application IDDateIncidents
123451/1/20173
123451/2/20177
123452/1/20172
123453/1/20178
123453/2/20174
123453/3/20179
123454/1/201712
123454/3/20175
123455/1/20176
123456/1/201710
123457/1/20174
123458/1/20178
123459/1/201717
1234510/1/20173
1234511/1/201712
1234512/1/201710
1234512/31/20172
123451/1/20183
123451/3/20185
123452/4/20184
123453/1/20189
123453/6/20182
123453/7/20187
123454/1/20186
123454/4/20187

 

What I am Looking for is How to get These?

Total 2017: 122

YTD 2017: 50

YTD 2018: 43

% Change (YTD)=(43-50)/50=-14%

 

Currently we are in April 2018 so YTD 2017 would count upto 4 April 2017 as YTD which is 50. 

 

Please Help

Thanks

Deepak

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Try these on for size:

 

Total 2017 = CALCULATE(SUM(Incidents[Incidents]),FILTER(ALL(Incidents),YEAR([Date])=2017))

YTD 2018 = TOTALYTD(SUM(Incidents[Incidents]),Incidents[Date])

YTD 2017 = CALCULATE([YTD 2018],SAMEPERIODLASTYEAR(Incidents[Date]))

% Change YTD = ([YTD 2018]-[YTD 2017])/[YTD 2017]

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

Try these on for size:

 

Total 2017 = CALCULATE(SUM(Incidents[Incidents]),FILTER(ALL(Incidents),YEAR([Date])=2017))

YTD 2018 = TOTALYTD(SUM(Incidents[Incidents]),Incidents[Date])

YTD 2017 = CALCULATE([YTD 2018],SAMEPERIODLASTYEAR(Incidents[Date]))

% Change YTD = ([YTD 2018]-[YTD 2017])/[YTD 2017]

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg,

I would accept the solution for the data I Posted. For My Dat aset which has 2 million rows and duplicated dates, I used my Knowledge of Tableau to get the Results. I did Similar calcuilations  in Power Bi by finding the functions I use in TABLEAU and got correct values.

Thanks a lot Sir!!!

Regards

Deepak

Ah yes, duplicate dates would cause some issues. If you get a chance, post your solution, might help someone else out!


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

Thanks a lot. I would get back  to you once I apply these to my actual data set. I am sure that this is what I am looking for.

Thanks

Deepak

 

Sure, just for reference, my table was calle "Incidents". Also, those are all measures.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I am Getting Error for YTD 2018...

The thing is my actual dataset has Duplicate dates so I am getting this Error:

 

Date Error.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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