DAX for Caculating for a Filtered Value During Specific Time

Hi Anonymous ,

---

 

The main issue I'm having is that sometimes an email will be opened or clicked that was not sent during the time range I'm trying to show, so open rate will be innacurate.

 

---

How do you judge that the email has not been sent? Is the time of "open" or "click" earlier than "sent"?

If so, try to create a measure like so:

Measure =
VAR DateTime_ =
    CONVERT ( 'Table'[Date] & " " & 'Table'[Time], DATETIME )
VAR UUID_ =
    MAX ( 'Table'[UUID] )
VAR Sent_DateTime =
    CALCULATE (
        DateTime_,
        FILTER (
            ALLSELECTED ( 'Table' ),
            MAX ( MAX ( 'Table'[UUID] ) = UUID_ && MAX ( 'Table'[EmailAction] ) = "sent" )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MAX (
                MAX ( 'Table'[UUID] ) = UUID_
                    && MAX ( 'Table'[EmailAction] ) = "open"
                    || MAX ( 'Table'[EmailAction] ) = "click"
            )
                && DateTime_ > Sent_DateTime
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.