Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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:
By the way, last time I checked, 688-4=684, not 683.
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:
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!
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |