Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I need to calculate the average of the difference between two dates.
The date computation formula works correct (rightmost column) but the average is not because sometimes an ID appears twice and is duplicated because the updatedAt column has two different times for the same to stage name. The result in circle should be 13 instead of 12,25.
What can I add in the formula to remove the duplicate value, so that the 10 value appears only once?
Solved! Go to Solution.
Hi @BaronSdG ,
Please try:
TESTBaSE DATEDIFF_VerbalOff-Offer =
VAR DateVerbalOfee =
MAXX (
FILTER (
LevOpportunity_stageChanges,
LevOpportunity_stageChanges[toStageId] = "dd5d6c89-dc9c-4efb-a62d-f6fa570cdd5e"
&& EARLIER ( LevOpportunity_stageChanges[mainId] ) = LevOpportunity_stageChanges[mainId]
),
LevOpportunity_stageChanges[updatedAt]
)
VAR DateOffer =
MAXX (
FILTER (
LevOpportunity_stageChanges,
LevOpportunity_stageChanges[toStageId] = "1afee169-6a11-4e0f-9ebd-5a01c8df3f1e"
&& EARLIER ( LevOpportunity_stageChanges[mainId] ) = LevOpportunity_stageChanges[mainId]
),
LevOpportunity_stageChanges[updatedAt]
)
VAR DateDifference =
IF (
ISBLANK ( DATEDIFF ( DateVerbalOfee, DateOffer, DAY ) ),
DATEDIFF ( DateVerbalOfee, TODAY (), DAY ),
DATEDIFF ( DateVerbalOfee, DateOffer, DAY )
)
RETURN
DateDifference
Measure =
DIVIDE (
SUMX (
SUMMARIZE (
'LevOpportunityApp_applications',
'LevOpportunityApp_applications'[mainId],
"total", [TESTBaSE DATEDIFF_VerbalOff-Offer]
),
[total]
),
COUNTROWS (
SUMMARIZE (
'LevOpportunityApp_applications',
'LevOpportunityApp_applications'[mainId]
)
)
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @BaronSdG ,
Please try:
TESTBaSE DATEDIFF_VerbalOff-Offer =
VAR DateVerbalOfee =
MAXX (
FILTER (
LevOpportunity_stageChanges,
LevOpportunity_stageChanges[toStageId] = "dd5d6c89-dc9c-4efb-a62d-f6fa570cdd5e"
&& EARLIER ( LevOpportunity_stageChanges[mainId] ) = LevOpportunity_stageChanges[mainId]
),
LevOpportunity_stageChanges[updatedAt]
)
VAR DateOffer =
MAXX (
FILTER (
LevOpportunity_stageChanges,
LevOpportunity_stageChanges[toStageId] = "1afee169-6a11-4e0f-9ebd-5a01c8df3f1e"
&& EARLIER ( LevOpportunity_stageChanges[mainId] ) = LevOpportunity_stageChanges[mainId]
),
LevOpportunity_stageChanges[updatedAt]
)
VAR DateDifference =
IF (
ISBLANK ( DATEDIFF ( DateVerbalOfee, DateOffer, DAY ) ),
DATEDIFF ( DateVerbalOfee, TODAY (), DAY ),
DATEDIFF ( DateVerbalOfee, DateOffer, DAY )
)
RETURN
DateDifference
Measure =
DIVIDE (
SUMX (
SUMMARIZE (
'LevOpportunityApp_applications',
'LevOpportunityApp_applications'[mainId],
"total", [TESTBaSE DATEDIFF_VerbalOff-Offer]
),
[total]
),
COUNTROWS (
SUMMARIZE (
'LevOpportunityApp_applications',
'LevOpportunityApp_applications'[mainId]
)
)
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thanks for the reply and sorry if I didnt specify earlier, but the data I provided before are columns, not measures, so your formula cannot pick them
User | Count |
---|---|
98 | |
90 | |
84 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |