Projects Budgets Versions Differences

[tabuzahra]

Good Day All,

Kindly, I'm new in Power BI and I don't have that much of experience in writing the DAX.

My scenario is that I have multiple projects, and each project has multiple Budget Versions which includes (Budget Version Name, Baseline Version Date, Baseline Efforts in Hours) and I would like to get a new DAX to calculate the different of:

1. Baseline Efforts in Hours between First version and the Last version of each project (Column "First Version VS Latest Version (Efforts In Hours)")
2. The percentage of difference between First version and the Last version of each project (Column "First Version VS Latest Version in percentage")

Below is a sample of data:

Project Name	Baseline Version Name	Baseline Version Date	Baseline Efforts in Hours	First Version VS Latest Version Efforts In Hours	First Version VS Latest Version in percentage
X	V1	1/1/2022	2022
X	V2	4/12/2022	1500
X	V3	7/15/2022	3000
X	V4	1/1/2023	3500
X	V5	2/5/2023	4000
A	V1	4/1/2022	500
A	V2	8/20/2022	800
A	V3	1/20/2023	950
U	V1	3/4/2022	1000
U	V2	5/2/2022	1250
U	V3	7/2/2022	1355
U	V4	11/2/2022	1600
U	V5	1/15/2022	1955

[tabuzahra]

Thanks Jianb, I will try the dax and i'll keep you posted 

[v-jianboli-msft]

Hi @tabuzahra,

Please try:

First Version VS Latest Version Efforts In Hours = 
var _a = MINX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])),VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1)))
var _b = SUMX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])&&VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1))=_a),[Baseline Efforts in Hours])
var _c = MAXX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])),VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1)))
var _d = SUMX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])&&VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1))=_c),[Baseline Efforts in Hours])
return _d-_b

First Version VS Latest Version in percentage = 
var _a = MINX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])),VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1)))
var _b = SUMX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])&&VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1))=_a),[Baseline Efforts in Hours])
var _c = MAXX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])),VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1)))
var _d = SUMX(FILTER('Table',[Project Name]=EARLIER('Table'[Project Name])&&VALUE(RIGHT([Baseline Version Name],LEN([Baseline Version Name])-1))=_c),[Baseline Efforts in Hours])
return DIVIDE(_d-_b,_d)

Final output:

Best Regards,

Jianbo Li

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