cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need Help in calculate the running Projects cost variation

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Frequent Visitor
 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
Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.