Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi all,
i want to calculate the moving average length of employment in weeks. I created the following measure:
Employee ID | Start | End |
001 | 1 jan 2021 | 31 jan 2021 |
001 | 1 feb 2021 | 31 jan 2022 |
002 | 1 mar 2021 | |
003 | 1 mar 2021 | 31 mar 2021 |
003 | 1 apr 2021 | 1 jun 2022 |
Etc | ||
Does anyone know ho i can get this result?
Thanks in advance,
Regards, Frank
Solved! Go to Solution.
@frankhofmans
Please check the attched file
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@frankhofmans
Create the following measure and give it try:
Avg Weeks =
VAR __WeeksTable =
ADDCOLUMNS (
ADDCOLUMNS (
VALUES ( Employments[Employee ID] ),
"@StartDate", CALCULATE ( MIN ( Employments[Start] ) ),
"@EndDate", COALESCE ( CALCULATE ( MAX ( Employments[End] ) ), MAX('Date table'[Date]) )
),
"Weeks", DATEDIFF ( [@StartDate], [@EndDate], WEEK )
)
RETURN
CALCULATE ( AVERAGEX ( __WeeksTable, [Weeks] ) )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
hi Fowmy,
Thanks for your reply. I can't duplicate the formula. 2 times addcolumns? and the "@" parts in the formula are also VAR's? If i copy the formula, i can't select "Weektabel" after the calculate(averagex....
Thanks in advance!
Regards,
Frank
@frankhofmans
Please an example data in a PBIX file, you can save the file in Google Drive, One Drive etc and share the link here.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
hi, this is the link,
https://drive.google.com/file/d/1FBm65fcRqLRTEl3WjCG0LdiGRt9Bj-u9/view?usp=sharing
thanks!
@frankhofmans
Please check the attched file
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |