Want to get the difference of the data between two consecutive years

[Anonymous]

Hi All,

Want a help to calculate the differece between the count of data (Incidence numbers) of two consecutive years data.

I have data like this (here I am showing in matrix form to make it clear) based upon the assignment group and want to calculate the difference/deviation of two year data. Please find it here.So what I want the deviation of these two year data on the basis of application (the data is the count of incidences).But there is a problem with this...we have duplicate dates in the incidence creation field (like we can get the multiple incidences on the same day) that why I cant use the time intelligence function like sameperiodlastyear or YTD etc.

Please can I get a helpful hand to solve this issue.

 Thanks in advance!

[rajulshah]

Hello @Anonymous,

Assuming this is your sample data, you can create a calculated column using DATEDIFF function.

You can refer it to the below link:

https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax

[Anonymous]

Actually I want the data difference of the corrosponding year not the date difference!

[rajulshah]

Can you please share your sample data?

[Anonymous]

Number    Assignment group    Created
INC0927583    AMS IBM PRODUCTION    28-08-2018 14:46:06
INC0927582    AMS IBM HEILITE    28-08-2018 14:46:04
INC0927539    AMS IBM LOGISTICS MM    28-08-2018 14:15:33
INC0927511    AMS IBM SAP EWM    28-08-2018 14:00:08
INC0927508    AMS IBM EROOM SHAREPOINT    28-08-2018 13:59:27
INC0927492    AMS IBM SRM    28-08-2018 13:49:04
INC0927356    AMS IBM FINANCE    28-08-2018 12:40:18
INC0639316    AMS IBM HEILITE    30-12-2017 14:02:17
INC0639312    AMS IBM LOGISTICS SD    30-12-2017 13:07:15
INC0639115    AMS IBM HEILITE    29-12-2017 17:28:06
INC0639107    AMS IBM FINANCE    29-12-2017 17:14:32
INC0639105    AMS IBM LOGISTICS SD    29-12-2017 17:11:40
INC0639073    AMS IBM SRM    29-12-2017 16:17:57
INC0639035    AMS IBM LOGISTICS MM    29-12-2017 15:12:08
INC0639029    AMS IBM CRM    29-12-2017 15:03:58
INC0639000    AMS IBM SEM    29-12-2017 14:21:27
INC0638990    AMS IBM SEM    29-12-2017 14:13:57
INC0638968    AMS IBM FINANCE    29-12-2017 13:57:08
INC0638965    AMS IBM HEILITE    29-12-2017 13:53:52
INC0638963    AMS IBM HEILITE    29-12-2017 13:47:35
INC0638950    AMS IBM LOGISTICS SD    29-12-2017 13:22:57
INC0638947    AMS IBM LOGISTICS SD    29-12-2017 13:18:02
INC0638914    AMS IBM FINANCE    29-12-2017 12:46:45
INC0638888    AMS IBM LOGISTICS MM    29-12-2017 12:19:09
INC0638869    AMS IBM FINANCE    29-12-2017 12:02:42

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made an sample based on your data.

Firstly, create a calculated column.

year = YEAR(Table1[Created])

Then create the measures as below.

2017 = CALCULATE(COUNT(Table1[NUMBER]),FILTER(Table1,Table1[year]=2017))

2018 = CALCULATE(COUNT(Table1[NUMBER]),FILTER(Table1,Table1[year]=2018))

de = [2018]-[2017]

Then we can get the result as we excepted.

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.

https://www.dropbox.com/s/lvoh0mvsplbwd9m/Want%20to%20get%20the%20difference.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank