Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Date | Incidents |
12345 | 1/1/2017 | 3 |
12345 | 1/2/2017 | 7 |
12345 | 2/1/2017 | 2 |
12345 | 3/1/2017 | 8 |
12345 | 3/2/2017 | 4 |
12345 | 3/3/2017 | 9 |
12345 | 4/1/2017 | 12 |
12345 | 4/3/2017 | 5 |
12345 | 5/1/2017 | 6 |
12345 | 6/1/2017 | 10 |
12345 | 7/1/2017 | 4 |
12345 | 8/1/2017 | 8 |
12345 | 9/1/2017 | 17 |
12345 | 10/1/2017 | 3 |
12345 | 11/1/2017 | 12 |
12345 | 12/1/2017 | 10 |
12345 | 12/31/2017 | 2 |
12345 | 1/1/2018 | 3 |
12345 | 1/3/2018 | 5 |
12345 | 2/4/2018 | 4 |
12345 | 3/1/2018 | 9 |
12345 | 3/6/2018 | 2 |
12345 | 3/7/2018 | 7 |
12345 | 4/1/2018 | 6 |
12345 | 4/4/2018 | 7 |
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
Solved! Go to Solution.
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]
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]
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!
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.
I am Getting Error for YTD 2018...
The thing is my actual dataset has Duplicate dates so I am getting this Error:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |