Forum Discussion
Helper IIIDinamic Month over Month change calculation
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 |
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.
5 Replies
- amitchandak
Super User
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)
tomekmHello,
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.
- 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).
v-yalanwu-msftCommunity Support
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!
