Need Help in calculate the running Projects cost variation

[eghusseinr]

As the list below is a sample,

I want to make a Dax "Formula" that calculate the Delta in value from month "M" to previous Month "M-1" to each project. But also exclude the first date value of that project from this formula.

i.e. Project Number :PF-10329438 , the first record was in Oct. 2022, 
so: Delta November for PF-10329438 = Oct. value - Nov. value
Delta December for PF-10329438 = Nov. value - Dec. value

another example
PF-XXXXX only have recorded in Apr. 2023 ==> to be excluded from formula as long as there are no other records for that project in the list, but next moth if the project "PF-XXXXX" get another record,
so: Delta May for PF-XXXXX = Apr. value - May value

[eghusseinr]

Project number	Value	Date
PF-10329438	EGP 127,194	10-24-2022
PF-10358883	EGP 2,336,586	10-24-2022
PF-10473993	EGP 6,462,543	10-24-2022
PF-11182775	EGP 338,486	10-24-2022
PF-38105310	EGP 2,776	10-24-2022
PF-10329438	EGP 127,194	11-22-2022
PF-10358883	EGP 2,336,586	11-22-2022
PF-10473993	EGP 6,462,543	11-22-2022
PF-11121349	EGP 58,393	11-22-2022
PF-11182775	EGP 336,577	11-22-2022
PF-38105310	EGP 2,776	11-22-2022
PF-10329438	EGP 127,194	12-16-2022
PF-10358883	EGP 2,336,586	12-16-2022
PF-10473993	EGP 6,467,543	12-16-2022
PF-11121349	EGP 58,393	12-16-2022
PF-11182775	EGP 336,577	12-16-2022
PF-38105310	EGP 2,776	12-16-2022
PF-11121349	EGP 58,393	1-19-2023
PF-11182775	EGP 401,017	1-19-2023
PF-38105310	EGP 2,776	1-19-2023
PF-11121349	EGP 58,393	2-2-2023
PF-11182775	EGP 401,017	2-2-2023
PF-11121349	EGP 58,393	3-2-2023
PF-11182775	EGP 401,017	3-2-2023
PF-11121349	EGP 58,393	4-4-2023
PF-11182775	EGP 401,017	4-4-2023

[v-jingzhang · Accepted solution]

Hi @eghusseinr 

Currently your [Value] column has the prefix "EGP " so it will be formatted to Text type and cannot compute the delta directly. You have to remove "EGP " and change it into number type, or create a new column to extract only the number values after "EGP ". For example, I add a [Value only] column to have only number values from [Value], and add another [Month] column based on [Date].

Then you can create the following measure to compute the delta value. Add it into a visual along with [Project number] and [Month] to see the results. 

Delta = 
var _previousValue = CALCULATE(SUM('Table'[Value only]),ALLEXCEPT('Table','Table'[Project number]),'Table'[Month]=YEAR(EDATE(MAX('Table'[Date]),-1))*100+MONTH(EDATE(MAX('Table'[Date]),-1)))
var _currentValue = SUM('Table'[Value only])
return
IF(ISBLANK(_previousValue),BLANK(),_previousValue - _currentValue)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.