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 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 NameBaseline Version NameBaseline Version DateBaseline Efforts in HoursFirst Version VS Latest Version Efforts In HoursFirst Version VS Latest Version in percentage
XV11/1/20222022  
XV24/12/20221500  
XV37/15/20223000  
XV41/1/20233500  
XV52/5/20234000  
AV14/1/2022500  
AV28/20/2022800  
AV31/20/2023950  
UV13/4/20221000  
UV25/2/20221250  
UV37/2/20221355  
UV411/2/20221600  
UV51/15/20221955  

 

Need Help

2 Replies

  • 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.