Year Over Year Comparison Excluding Year

[chq]

My data looks like this....

Date		Ticket A	Ticket B	Ticket C	Ticket D	Ticket E	Ticket F
01/01/2020		0	0	0	0	0	0
01/01/2019		18	14	1	15	150	490
01/01/2018		14	14	1	12	154	500
01/01/2021		3	0	0	5	75	600
01/02/2020		0	0	0	0	0	0
01/02/2019		19	15	1	15	150	490
01/02/2021		3	0	1	5	76	604
01/02/2018		15	15	1	14	155	505

and my desired output is something like this....

		2018	Difference	% Diff	2019	Difference	% Dif	2021
01/01		695	-7	-1.07%	688	-4	-0.73%	683
01/02		705	-15	-2.13%	690	-1	-0.14%	689

The data goes back to 2005 and I would need to compare them by date going back year over year (exlcluding 2020 from the data set) Are there a set of measures that can be used to get this kind of result? So far my formulas have not worked, but I have a feeling it is my inexperience with DAX.

Thanks in advance

[lbendlin · Accepted solution]

DAX is only part of your issue.  Your source data is in an unfortunate format. I would recommend you transform it into something a bit more useful

Table Tickets:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9dCsAgDIPv0mdhtbNOzzK8/zVmCv6ywcAG1I8kvW9if9QjLEyO3qe4gflcn3yCBAhGTcCGvNED7LQYjavySgv+zykaxhckTqT8Kyu9bO4dv8vKHu9bfLT4sPqm4dR8bT9VW0uplAc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ticket A" = _t, #"Ticket B" = _t, #"Ticket C" = _t, #"Ticket D" = _t, #"Ticket E" = _t, #"Ticket F" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}})
in
#"Changed Type1"

That will then allow you to create the measures in DAX a bit easier. In fact, since you are pivoting this by day and year, let's add these columns to the table directly:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9dCsAgDIPv0mdhtbNOzzK8/zVmCv6ywcAG1I8kvW9if9QjLEyO3qe4gflcn3yCBAhGTcCGvNED7LQYjavySgv+zykaxhckTqT8Kyu9bO4dv8vKHu9bfLT4sPqm4dR8bT9VW0uplAc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Ticket A" = _t, #"Ticket B" = _t, #"Ticket C" = _t, #"Ticket D" = _t, #"Ticket E" = _t, #"Ticket F" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each Date.ToText([Date],"MM/dd"))
in
#"Added Custom1"

Then in DAX you can create your measures:

Difference :=

var y = SELECTEDVALUE(Tickets[Year])

var yp = CALCULATE(max(Tickets[Year]),Tickets[Year]<y,Tickets[Year]<>"2020")

return sum(Tickets[Value])-CALCULATE(sum(Tickets[Value]),Tickets[Year]=yp)

Diff % :=

var y = SELECTEDVALUE(Tickets[Year])

var yp = CALCULATE(max(Tickets[Year]),Tickets[Year]<y,Tickets[Year]<>"2020")

var d = CALCULATE(sum(Tickets[Value]),Tickets[Year]=yp)

return divide(sum(Tickets[Value])-d,d)

and then place the measures in the matrix BEFORE the value

 

By the way, last time I checked, 688-4=684, not 683.

[chq]

You just helped me tremendously thank you so much this is wonderful. 

Good catch on the math!

Thank you so very much again!