Forum Discussion
Helper IIYear Over Year Comparison Excluding Year
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
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 valueBy the way, last time I checked, 688-4=684, not 683.
2 Replies
- lbendlin
Super User
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 valueBy the way, last time I checked, 688-4=684, not 683.
chqYou just helped me tremendously thank you so much this is wonderful.
Good catch on the math!
Thank you so very much again!