How to show data in the same period but different year in a table?

[Anonymous]

Hi everyone!

I'm new to Dax and hoping some one could help me.

I imported 2 excel files into Power BI. One has 2021 sales data from Jan 1- Oct 1. One has 2020 sales data from Jan 1 - Dec 31. I wanted to create a table and show below info in power bi:

Column A = Merchant names, Column B = 2021 Jan 1- Oct 1 Sales, Column C = 2020 Jan 1 - Oct 1 Sales, Column D = Column B-C

What I did:

I created a calendar with dates from 2020/1/1-2021/12/31 and tried to use it as a slicer and filter the period 2020/1/1-2021/10/31. However, this way I wouldn't be able to exclude 2020 Oct 2- Dec 31 data from Column C.

I learnt about 'sameperiodlastyear' but the youtube videos was showing how to use this function to find last year's data when the data of the two years are in the same table/column.

My questions are:

1. If  2020 and 2021 data are in two different tables, how to create a table in Power BI similar to the table below to show sales data in the same period of 2021 and 2020 (Column B and C)?

2. How to create a calculation to show the differences in sales between same period in two different years (column C)?

Thank you very much for your help in advance!

[v-yingjl]

Hi @Anonymous ,

1. If 2020 and 2021 data are in two different tables, you can create a calendar table a a new Merchant table, build relationships with the two source tables

Calendar = CALENDAR("2020/1/1","2021/12/31")

Merchant table = DISTINCT('2021 Sales Table'[Merchant])

Create two measures to calculate Sales:

2021 Jan1 - Oct1 Sales = SUM('2021 Sales Table'[Sales])

2020 Jan1 - Oct1 Sales = 
CALCULATE (
    SUM ( '2020 Sales Table'[Sales] ),
    DATEADD ( 'Calendar'[Date], -1, YEAR )
)

2. Create another measure to calculate the difference:

Difference in Sales = [2021 Jan1 - Oct1 Sales] - [2020 Jan1 - Oct1 Sales]

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Is there a reason you cannot combine both of your tables into a single one? It's generally not a good idea to have separate tables for separate years.

It'll be easier to work with if your data is formatted into a single table like this:

Merchant	Year	Sales
A	2020	4000
B	2020	7000
C	2020	1500
D	2020	4200
A	2021	5000
B	2021	3000
C	2021	1200
D	2021	3200

[Anonymous]

Hi Alexis,

Thank you for your message.

The original files I received looks like this:

1st file - Monthly Sales by Company. Instead of placing horizontally, all information are organised vertically.

2nd file - Total Sales by Company (no 'year' column shown)

In addition to organise data from different years in a single table, is there any other things you suggest that I should keep in mind when formatting the data before using it in Power BI (based on my screenshots of the orginal files)?

Thank you!

Cathy

[AlexisOlson · Accepted solution]

I'd recommend transforming each table from this format

Into this format

for each separate year and then appending them all together.

Here's an example query you can paste into the Advanced Editor and then click through the steps to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDAAUqYgwhDCNgIRxhC2oalSrE60khNCxNgAoQvMNjaFsCEqnUGyEBFDAwQbYr4pTBdIpQuSiCnCdjMkXSZAlbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"Jan Total ($)" = _t, #"Jan Total Units" = _t, #"Feb Total ($)" = _t, #"Feb Total Units" = _t, #"Mar Total ($)" = _t, #"Mar Total Units" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Jan Total ($)", Int64.Type}, {"Jan Total Units", Int64.Type}, {"Feb Total ($)", Int64.Type}, {"Feb Total Units", Int64.Type}, {"Mar Total ($)", Int64.Type}, {"Mar Total Units", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Month", "Metric"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Metric]), "Metric", "Value"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Year", each 2020, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each Date.FromText([Month] & " " & Number.ToText([Year])), type date)
in
    #"Added Custom1"

[Anonymous]

Thank you Alexis for spending your precious time to help me!!I'm really beyond appreciative of all your help!! I truly learnt so much from you! I will give it a try and let you know how it works!!! 🙂