Forum Discussion

tomekm's avatar
tomekm
Icon for Helper III rankHelper III
5 years ago
Solved

Dinamic 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 numb...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
    tomekm
    Icon for Helper III rankHelper III
    5 years ago

    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.