CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Jim Doakes
Jim Doakes

Reputation: 11

Power Query/DAX to calculate monthly raw sales figure

Dear stackoverflow, please help!

I'm hoping for some assistance with data processing in Power BI, either using Power Query or DAX. At this point I am really stuck and can't figure out how to solve this problem.

The below table is a list of sales by Product, Month, and Year. The problem with my data is that the value in the sales data is actually cumulative, rather than the raw figure of sales for that month. In other words, the figure is the sum of the number of sales for the month (for that Year and Product combination) and the number of sales for the preceding month. As you will see in the table below, the number gets progressively larger in each category as the year progresses. The true number of sales for TVs in Feb of 2021, for example, is the sales figure of 3 minus the corresponding figure for sales of TVs in Jan of 2021 (1).

I really would appreciate if anyone knows of a solution to this problem. In reality, my table has hundreds of thousands of rows, so I cannot do the calculations manually.

Is there a way to use Power Query or DAX to create a calculated column with the Raw Sales figure for each month? Something that would check if Product and Year are equal, then subtract the Jan figure from the Feb figure and so on?

Any help will be very much appreciated,

Sales Table

Product Sales (YTD) Month Year
TV 1 Jan 2021
Radio 4 Jan 2021
Cooker 5 Jan 2021
TV 3 Feb 2021
Radio 5 Feb 2021
Cooker 6 Feb 2021
TV 3 Mar 2021
Radio 6 Mar 2021
Cooker 8 Mar 2021
TV 5 Apr 2021
Radio 7 Apr 2021
Cooker 8 Apr 2021
TV 7 May 2021
Radio 8 May 2021
Cooker 8 May 2021
TV 9 Jun 2021
Radio 10 Jun 2021
Cooker 10 Jun 2021
TV 10 Jul 2021
Radio 10 Jul 2021
Cooker 10 Jul 2021
TV 11 Aug 2021
Radio 13 Aug 2021
Cooker 12 Aug 2021
TV 11 Sep 2021
Radio 13 Sep 2021
Cooker 12 Sep 2021
TV 12 Oct 2021
Radio 14 Oct 2021
Cooker 13 Oct 2021
TV 17 Nov 2021
Radio 19 Nov 2021
Cooker 17 Nov 2021
TV 19 Dec 2021
Radio 20 Dec 2021
Cooker 20 Dec 2021
TV 4 Jan 2022
Radio 2 Jan 2022
Cooker 3 Jan 2022
TV 5 Feb 2022
Radio 3 Feb 2022
Cooker 5 Feb 2022

Thanks, Jim

Upvotes: 0

Views: 680

Answers (1)

horseyride
horseyride

Reputation: 21393

Give this a try in powerquery / M. It groups on Product and Year, then sorts the months, and subtracts each row from the next row to determine the period amount.

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Product", "Year"}, {
    {"data", each 
    let r=Table.Sort(Table.AddIndexColumn(_, "Index", 0, 1),{ each List.PositionOf({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, [Month]), {"Month",Order.Ascending}}),
    x= Table.AddColumn( r,  "Period Sales", each if [Index]=0 then [#"Sales (YTD)"] else [#"Sales (YTD)"]-r{[Index]-1}[#"Sales (YTD)"])
    in x
    , type table }
    }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Sales (YTD)", "Month", "Period Sales"}, {"Sales (YTD)", "Month", "Period Sales"})
in  #"Expanded data"

enter image description here

Upvotes: 1

Related Questions