Year on Year % change - [Text Headers]

[LUCASM]

There are probably much better ways to go about what I have done so far, but there you go. I am stuck with what I have now and in need of calculating the Year on Year % change of sales accross 5 countries.

My data from SQL Server sums the sales by country and due to the nature of the stored procedure I am left with a text value for the date ie Nov 2017, Nov 2018 with Countries and total sum of sales underneath like this. As this is not formated as a date I cannot use any of the Time Intelligence formulas!

In order to render the report totally dynamic I am using DirectQuery - maybe this is bad thinking but I do not want to have to manually refresh the dataset 4 times a day to get ensure the latest data is available - is my thinking on this wrong?

Country	Nov 2017	Nov 2018
IT	40883	52777
DE	105138	104110
FR	99509	86349
ES	17933	21413
DE	131361	168110

What I am trying to achieve is this

Country	Nov 2017	Nov 2018	%Change
IT	40883	52777	29%
DE	105138	104110	-1%
FR	99509	86349	-13%
ES	17933	21413	19%
DE	131361	168110	28%

[v-chuncz-msft · Accepted solution]

@LUCASM,

Metadata for the result set can be defined by using the WITH RESULT SETS options.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-20...

[LUCASM]

@v-chuncz-msft

Kudos to you for replying on Christmas eve not sure my wife would have been so obliging.

This may be the accepted solution, however it is so vague that I am struggling to follow it.

Looking at the link I cannot fathom out what I am supposed to be looking for or at.

As far as I can tell what is being suggested is that my (this) Year on (last) Year columns names can be set to the contents of the values ie 2017 and 2018 by using the WITH RESULT SETS option - how exactly.

Would it help if I posted my current code?

Martin

[LivioLanzo]

The transformation to apply within Power Query would be something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYw9DsAgCEbvwuwAggJ7bdK17Wa8/zWKTJ2+37w54XqhgKAZh7aqqrDKhGNEJGzElkaIMIfzjuze0EOts3jW49k3dd6YSkL8wzBxp226JWZ9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, #"Nov 2017" = _t, #"Nov 2018" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Nov 2017", Int64.Type}, {"Nov 2018", Int64.Type}}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(ChangedType, {"Country"}, "MonthYear", "Amount"),
    TransformMonthColumns = Table.TransformColumns(UnpivotedOtherColumns, {"MonthYear", each Date.From(" 1 " & _), type date})
in
    TransformMonthColumns

[LUCASM]

Hi @LivioLanzo

I have the underlying data available in a 2nd report in the Power Bi report.

I did already try posting this question earlier using the data you suggest but the post got spammed and i ended up delteing it  it and on further analysis created this smaller version with only the months at either end of my sample.

In my initial dataset I have weekly data [W.E Date] date and [Country] text and [Sales] decimal

After Christmas I will return to this as I need to get it up and running by February...

Thanks for your interest in this.

[LivioLanzo]

Hello @LUCASM,

with some transformations within Power Query it is possible to get the data in the right format to then use Time Intelligence or at least create a monthly calendar. But before going this route, I would explore the possibility on your side to access the underlying table in SQL Server instead of the store procedure, maybe creating your own view in the database? This would save you from having to do too much (unneeded) work within Power Query and get the data already as you want to have it.