Delta between sales in particular months

Question

I have huge data set with sales from the last 2 years which is organized in following way:

I was asked to prepare a report which compares sales between mth in 2022 and corresponding mth in 2023 and expresses it in delta/ variance.

I need to prepare it in Power BI.

Then the same report should contain similar variance between year quarters.

Can you please help me?

Thank you in advance

I need to have a table with variance between sales in particular months of 22 and 23 in power BI.

Answer 1

In powerbi (powerquery) using M, you could do this

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "PriorYearMonth", each Text.Start([YearMonth],4)&Text.From(Number.From(Text.End([YearMonth],2))-1)),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Priorquarter", each Text.Start([quarter],3)&Text.From(Number.From(Text.End([quarter],2))-1)),
#"Merged Queries" = Table.NestedJoin(#"Added Custom2", {"PriorYearMonth"}, #"Added Custom2", {"YearMonth"}, "xxx", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "xxx", {"sales value"}, {"Prior.sales"}),
#"Added Custom3" = Table.AddColumn(#"Expanded Table", "Variance", each [sales value]-[Prior.sales]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Title", each [YearMonth]&"/"&[PriorYearMonth]),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "QuarterColumns", each [quarter]&"/"&[Priorquarter]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom5",{"Product", "Variance", "Title"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Added Custom5",{"QuarterColumns", "Variance", "Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns2",{{"QuarterColumns", "Title"}}),
Combined = #"Removed Other Columns" & #"Renamed Columns",
#"Filtered Rows" = Table.SelectRows(Combined, each ([Variance] <> null)),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Title]), "Title", "Variance", List.Sum)
in #"Pivoted Column"

Answer 2

I would recommend to write the following two measures:

Sales SY =
CALCULATE( SUM('Sales'[Sales Value]) )

Sales PY = 
CALCULATE( SUM('Sales'[Sales Value]), SAMEPERIODLASTYEAR(DIM_DATE[DATE]) ) 

Sales ∆ = [Sales SY] - [Sales PY]

Then create a DIM_DATE calendar table and make a relationship between the calendar table and your sales table. Maby change the date to the first of each Quarter. (Q1_23 = 01-01-2023 etc.) This will make sure you can make a 1 to many relationship instead of a many to many relations ship to your calendar table.

Then you only need to make a matrix with your products in the Rows. Quarer and year in the columns and the [Sales ∆] as the value. This will calculate the difference between the current year quarterly sales and the previous year in the same quarter.