Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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 |
Thanks Jianb, I will try the dax and i'll keep you posted
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |