Forum Discussion

tabuzahra's avatar
tabuzahra
Helper II
3 years ago

Projects Budgets Versions Differences

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...
Need Help
v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

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.