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.
User | Count |
---|---|
128 | |
71 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |