Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
In my historical project table I have created the following measure:
Solved! Go to Solution.
Hi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
Expected avg measure: =
VAR _maxdatebyIDtable =
CALCULATETABLE (
Data,
TREATAS (
GROUPBY (
Data,
Data[ID],
"@maxdate", MAXX ( CURRENTGROUP (), Data[Name ModifiedOn] )
),
Data[ID],
Data[Name ModifiedOn]
)
)
RETURN
DIVIDE (
SUMX ( _maxdatebyIDtable, Data[Days since modification] ),
COUNTROWS ( _maxdatebyIDtable )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your dataset looks like, but please try something like below and check whether it works for your dataset.
expected measure: =
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'projecttable'[projectID] ),
"@datediff", [TimeSinceModification]
),
[@datediff]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan,
I have tried to replicate the structure of my data tabel and hope it gives better meaning now:
E.g. you can see that I have filtered 6 unique project. But project 260 have 2 different modification date and it should only calculate the Lates modified.
So here the correct Average should be (roundet) 5
Hi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
Expected avg measure: =
VAR _maxdatebyIDtable =
CALCULATETABLE (
Data,
TREATAS (
GROUPBY (
Data,
Data[ID],
"@maxdate", MAXX ( CURRENTGROUP (), Data[Name ModifiedOn] )
),
Data[ID],
Data[Name ModifiedOn]
)
)
RETURN
DIVIDE (
SUMX ( _maxdatebyIDtable, Data[Days since modification] ),
COUNTROWS ( _maxdatebyIDtable )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I have implemented your solution and it works perfectly.
Thanks
User | Count |
---|---|
51 | |
37 | |
20 | |
14 | |
13 |
User | Count |
---|---|
96 | |
71 | |
29 | |
20 | |
13 |