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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
chq
Helper II
Helper II

Year Over Year Comparison Excluding Year

My data looks like this....

Date Ticket ATicket BTicket CTicket DTicket ETicket F
01/01/2020 000000
01/01/2019 1814115150490
01/01/2018 1414112154500
01/01/2021 300575600
01/02/2020 000000
01/02/2019 1915115150490
01/02/2021 301576604
01/02/2018 1515114155505

 

 

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

  2018Difference% Diff2019Difference% 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

1 ACCEPTED SOLUTION
lbendlin
Super User
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 value
 
lbendlin_2-1612314402345.pnglbendlin_3-1612314440509.png

 

 
 

 

 

 

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

View solution in original post

2 REPLIES 2
lbendlin
Super User
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 value
 
lbendlin_2-1612314402345.pnglbendlin_3-1612314440509.png

 

 
 

 

 

 

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

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

Good catch on the math!

Thank you so very much again! 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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