Calculated Column: net new over last day for each category

[ed7]

I have a table that has data similiar to the table below, I need to calculate net new for each date per category. For example second line would show net new of 33, fourth line net new is 3. I would prefer to use  a Calculated column not Measure. Tried different recommendation, nothing worked for Calculated Column (but some formulas worked to create Measure). How can I create a calc column for Net New?

Category	Date	Running total from year start
A	01/01/2020	12
A	01/02/2020	45
B	01/01/2020	2
B	01/02/2020	5

[Anonymous]

@ed7 

I believe EARLIER is best for you if you want a calculated column.

Column = 
var current_day  = CALCULATE(SUM('Table'[Runningtotal]),FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Category]=EARLIER('Table'[Category])))

var previous_day = CALCULATE(SUM('Table'[Runningtotal]),FILTER('Table','Table'[Date] = EARLIER('Table'[Date])-1 &&'Table'[Category]=EARLIER('Table'[Category])))

Return current_day - previous_day

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(ISBLANK(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Date]<EARLIER(Data[Date])))),BLANK(),Data[Running total from year start]-LOOKUPVALUE(Data[Running total from year start],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Date]<EARLIER(Data[Date]))),Data[Category],Data[Category]))

Hope this helps.