Subtract a value from another in same column, where other values are equal

[walker4545]

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.

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

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,

Jim Walker.

[Anonymous · Accepted solution]

To insert Monthly sales in your table in a column

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

To insert Monthly sales in your table in a column

[walker4545 · Accepted solution]

Great, this worked for me, with a minor change as I had to put currentQty - prevQty into CALCULATE() to make it work. I also added in IFERROR in the case that a product had no row for a certain month. 

Thanks very much!

The dax code is as follows:

Monthly Sales =

VAR currentDate = MONTH('sales new column'[Date])

VAR prevPeriod =

EOMONTH('sales new column'[Date], -1)

VAR currentProduct = 'sales new column'[Product]

VAR currentQty = 'sales new column'[Qty]

VAR prevQty =

IFERROR(LOOKUPVALUE(

'sales new column'[Qty],

'sales new column'[Date], prevPeriod,

'sales new column'[Product], currentProduct

) ,

0)

VAR result =

IF(

MONTH('sales new column'[Date]) = 1,

'sales new column'[Qty],

CALCULATE(currentQty - prevQty)

)

RETURN

result

//DAX Function to calculate raw monthly sales where the default Qty figure is an aggregate of the preceding month's Qty plus new Qty

[Anonymous]

Hi,

Excellent have a nice day

[rohit_singh]

Hi @Anonymous ,

You can create something similar to the below based on your requirement : 

Prev Month =

var _month = max('Cummulative Product Sales'[MonthStart])

Return
CALCULATE(MAX('Cummulative Product Sales'[MonthStart]),
FILTER(ALLSELECTED('Cummulative Product Sales'), 'Cummulative Product Sales'[MonthStart] < _month))

Prev Month Sales =

var _month = max('Cummulative Product Sales'[MonthStart])

var _product = max('Cummulative Product Sales'[Product])

Return
CALCULATE(MAX('Cummulative Product Sales'[Sales (YTD)]),
FILTER(ALLSELECTED('Cummulative Product Sales'), 'Cummulative Product Sales'[Product] = _product && 'Cummulative Product Sales'[MonthStart] < _month))

Curr Month Sales =

var _sales = sum('Cummulative Product Sales'[Sales (YTD)])
var _prev = [Prev Month Sales]

var _curr = _sales - _prev

return
_curr

Basically what we are trying to achieve is to fetch the values of the previous month and previous sales for each row. Once we have these values, we can compute whatever metrics we need.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  🙂

[Anonymous]

Hi

Do you have a table Date in your model ?