Get Sum for only net new records from month to month.

[Skunny11]

Hi, 

I am trying to display only the sum of net new records from month to month in dax. Right now I have two measures one for total sum count for each record and a measure for the previous months sum for each record of that month.

My base measure for this is:

Measure_Net_New_Monthly_Sum = 
CALCULATE
(
	SUM(mytable[SummedValue]),
        VALUES(mytable[uniqueName]),
        PREVIOUSMONTH(mytable[collectdate])
)

I need to add a filter something like how i would do with T-SQL:

SELECT name FROM mytable WHERE name NOT IN (SELECT name FROM mytable WHERE collectdate = DATEPART(m, DATEADD(m, -1, getdate())))

Is this possible in the filter?

[v-juanli-msft · Accepted solution]

Hi @Skunny11 

Create measures

Previous =
VAR L =
    MAX ( Sheet6[date] )
RETURN
    CALCULATE (
        SUM ( Sheet6[value] ),
        FILTER (
            ALL ( Sheet6 ),
            Sheet6[name] = MAX ( Sheet6[name] )
                && DATEDIFF ( Sheet6[date], L, MONTH ) = 1
        )
    )

new value = CALCULATE(SUM(Sheet6[value]),FILTER(Sheet6,[Previous]=BLANK()))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.