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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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:

Screenshot 2021-10-26 130548.png

 

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)?

 

Screenshot 2021-10-26 130548.png

 

Thank you very much for your help in advance!

1 ACCEPTED SOLUTION

I'd recommend transforming each table from this format

AlexisOlson_0-1635805598309.png

 

Into this format

AlexisOlson_1-1635805653787.png

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"

View solution in original post

5 REPLIES 5
v-yingjl
Community Support
Community Support

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])

vyingjl_0-1635475670753.png

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]

vyingjl_1-1635475739449.png

 

 

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
Super User
Super User

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
Not applicable

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.

 

Screenshot 2021-11-01 175855.png

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

Screenshot 2021-11-01 175526.png

 

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

I'd recommend transforming each table from this format

AlexisOlson_0-1635805598309.png

 

Into this format

AlexisOlson_1-1635805653787.png

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
Not applicable

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!!! 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.