Calculate average of a value only once per ID

[BaronSdG]

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?

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

CALCULATE(DateDifference, DISTINCT(LevOpportunityApp_applications[mainId]))

[v-cgao-msft · Accepted 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

[BaronSdG]

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