Dinamic Month over Month change calculation

[tomekm]

Hi,

I'm trying to create a column that will show the "Change" in Volume for each ID. Please see table below. If I filter on a specific month, I'd like to see the total of Changes for each ID number. Can you please help me with the DAX formula?

Thank you.

ID	Volume	Periods_Table	Change (expected outcome)
10	1	February 1, 2021	0
10	5	March 1, 2021	4
10	3	April 1, 2021	-2
20	2	March 1, 2021	0
20	3	April 1, 2021	3
30	12	February 1, 2021	0
30	13	March 1, 2021	1
30	4	April 1, 2021	-9
40	2	March 1, 2021	0
40	2	April 1, 2021	0

[v-yalanwu-msft · Accepted solution]

Hi, @tomekm ;

Please try:

new column = 
VAR _last =
    EOMONTH ( [Periods_Table], -1 )
RETURN
    IF (
        [Periods_Table]= CALCULATE ( MIN ( [Periods_Table] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        0,
        [Volume]- CALCULATE (SUM ( [Volume] ), FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[ID] ),
                    EOMONTH ( [Periods_Table], 0 ) = _last)))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomekm]

Thank you!

[tomekm]

The formula works but partially. Please see screenshot. Ideally I would want to show "+8" in the July row, instead of "-8" in June, as we are measuring the changes from previous month to current month (i.e. July).

[amitchandak]

@tomekm , Try a new column like

new colum =
var _period = eomonth([Periods_Table],-1)
var _id = [ID]
return
[Volume] - (sumx(filter(table, eomonth([Periods_Table],0) = _period && [ID] = _id),[Volume])+0)

[tomekm]

Hello,

Would you be able to help me with my previous post? "The formula works but partially. Please see screenshot. Ideally I would want to show "+8" in the July row, instead of "-8" in June, as we are measuring the changes from previous month to current month (i.e. July)."

Thank you.